reports.xml

一个java开源报表工具

<list>
  <reportdefinition>  
    <name>Customer and Suppliers by City</name>
    <sql>SELECT City, CompanyName, ContactName, 'Customers' AS Relationship 
FROM CUSTOMERS
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM SUPPLIERS ORDER BY City, CompanyName</sql>
    <queryType>STATIC_QUERY</queryType>
    <parameterDefinitions/>
  </reportdefinition>
  <reportdefinition>
    <name>Alphabetical list of products</name>
    <sql>SELECT PRODUCTS.*, CATEGORIES.CategoryName
FROM CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.CategoryID = PRODUCTS.CategoryID
WHERE PRODUCTS.Discontinued=0</sql>
    <queryType>STATIC_QUERY</queryType>
    <parameterDefinitions/>
  </reportdefinition>
  <reportdefinition>
    <name>Products Above Average Price</name>
    <sql>SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice&gt;(SELECT AVG(UnitPrice) From Products)
ORDER BY UnitPrice DESC</sql>
    <queryType>STATIC_QUERY</queryType>
    <parameterDefinitions/>
  </reportdefinition>
  <reportdefinition>
    <name>Products Above Price</name>
    <sql>SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice&gt; #Above Price#
ORDER BY UnitPrice DESC</sql>
    <queryType>SIMPLE_PARAMETER_QUERY</queryType>
    <parameterDefinitions>
      <parameterdefinition>
        <reportDefinition reference="../../.."/>
        <name>Above Price</name>
        <type>java.lang.Long</type>
        <optional>false</optional>
        <defaultValue>30</defaultValue>
      </parameterdefinition>
    </parameterDefinitions>
  </reportdefinition>
  <reportdefinition>
    <name>Product Sales</name>
    <sql>&lt;plain&gt;
SELECT Categories.CategoryName, Products.ProductName, Sum(OrderDetails.UnitPrice*Quantity*(1-Discount)) AS ProductSales
FROM Categories 
	INNER JOIN Products 
		ON Categories.CategoryID = Products.CategoryID 
	INNER JOIN OrderDetails 
		ON Products.ProductID = OrderDetails.ProductID 
	INNER JOIN Orders 
		ON Orders.OrderID = OrderDetails.OrderID
WHERE 1 = 1
&lt;/plain&gt;
&lt;dynamic type="isNotNull" append-before="AND Orders.ShippedDate &gt;= " parameter-name="Order Shipped Date From"/&gt;
&lt;dynamic type="isNotNull" append-before="AND Orders.ShippedDate &amp;lt;= " parameter-name="Order Shipped Date Thru"/&gt;
&lt;plain&gt;
GROUP BY Categories.CategoryName, Products.ProductName
&lt;/plain&gt;</sql>
    <queryType>DYNAMIC_PARAMETER_QUERY</queryType>
    <parameterDefinitions>
      <parameterdefinition>
        <reportDefinition reference="../../.."/>
        <name>Order Shipped Date From</name>
        <type>java.sql.Timestamp</type>
        <optional>true</optional>
        <defaultValue></defaultValue>
      </parameterdefinition>
      <parameterdefinition>
        <reportDefinition reference="../../.."/>
        <name>Order Shipped Date Thru</name>
        <type>java.sql.Timestamp</type>
        <optional>true</optional>
        <defaultValue></defaultValue>
      </parameterdefinition>
    </parameterDefinitions>
  </reportdefinition>
  <reportdefinition>
    <name>Top N Most Expensive Products</name>
    <sql>SELECT TOP #TOP Number# Products.ProductName, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC</sql>
    <queryType>SIMPLE_PARAMETER_QUERY</queryType>
    <parameterDefinitions>
      <parameterdefinition>
        <reportDefinition reference="../../.."/>
        <name>TOP Number</name>
        <type>java.lang.Integer</type>
        <optional>false</optional>
        <defaultValue>10</defaultValue>
      </parameterdefinition>
    </parameterDefinitions>
  </reportdefinition>
  <reportdefinition>
    <name>All Customers</name>
    <sql>select * from CUSTOMERS</sql>
    <queryType>STATIC_QUERY</queryType>
    <parameterDefinitions/>
  </reportdefinition>
</list>