Opentaps Analytics Report Generator

From Opentaps Wiki

The opentaps Analytics Report Generator is designed to help create and maintain a large number of similar reports which share similar fields and lookup queries. For example, from a common sales report, it can generate Sales by Country, by Brand, by Day, by Day of Week, by Week of Year, by Month, by Year, etc.

The automated reports are specified by a reports XML. Each report defines a report consisting of an xaction file, a properties file, and a Jasper Report (jrxml) report. As an example, look at the OrderReports.xml file in scripts/queries/:

<reports default-template="OrderReports.jrxml" default-xaction-template="default.xaction">
  <report name="SalesByBrand" target="Sales Analysis" title="Sales By Brand" description="Show the sales by brand.">
   <query>
    <fields>
      <field name="Brand" class="java.lang.String" select="PDIM.brand"/>
    </fields>
    <where><![CDATA[]]></where>
    <orderby><![CDATA[PDIM.brand]]></orderby>
    <groupby><![CDATA[PDIM.brand]]></groupby>
   </query>
  </report>
  <report name="SalesBySize" target="Sales Analysis" title="Sales By Size" description="Show the sales by size.">
   <query>
    <fields>
      <field name="Size" class="java.lang.String" select="PDIM.size"/>
    </fields>
    <where><![CDATA[]]></where>
    <orderby><![CDATA[PDIM.size]]></orderby>
    <groupby><![CDATA[PDIM.size]]></groupby>
   </query>
  </report>
<!-- more queries -->
</reports>

The reports tag defines a default Jasper report template, in this case OrderReports.jrxml, and a default Pentaho xaction template, in this case default.xaction for each report. Then, each report is defined by a report tag. The layout of the report is controlled by the Jasper report template, which has a placeholder for the SELECT fields, WHERE conditions, GROUP BY, and ORDER BY portions of the query, in this format:

<jasperReport
    name="${query.@name}"
<#-- ... --->
 SELECT
  <#list query.fields.field as field>
    ${field.@select} as ${field.@name},
  </#list>
  SUM(quantity) as UnitsSold,
  SUM(gross_amount_base_currency) as ValueSold,
  SUM(discount_amount_base_currency) as Discount,
  SUM(returned_quantity) as UnitsReturned,
  SUM(returned_net_amount_base_currency) as ValueReturned,
  SUM(returned_quantity)/SUM(quantity) as PercentReturned,
  SUM(quantity - returned_quantity) as NetUnitsSold,
  SUM(net_amount_base_currency - returned_net_amount_base_currency) as NetSales,
  (SELECT SUM(net_amount_base_currency - returned_net_amount_base_currency) FROM ORDER_ITEM_FACT) as TotalNetSales
  FROM
  ORDER_ITEM_FACT OIFACT
  LEFT OUTER JOIN PRODUCT_DIMENSION PDIM ON OIFACT.product_dim_key = PDIM.product_dim_key
  LEFT OUTER JOIN CUSTOMER_DIMENSION CDIM ON OIFACT.customer_dim_key = CDIM.customer_dim_key
  LEFT OUTER JOIN DATE_DIMENSION DDIM ON OIFACT.order_date_key = DDIM.date_dim_key
  <#if query.where != "">
  WHERE ${query.where}
  </#if>
  <#if query.groupby?has_content>
  GROUP BY
  ${query.groupby}
  </#if>
  <#if query.orderby?has_content>
  ORDER BY
  ${query.orderby}
  </#if>]]></queryString>
<#-- more JRXML -->

The standard report mounting ex-action is specified in the xaction template file.

When opentaps analytics is built, the reports will be automatically compose together from the three templates and generated in their target directories.

The report element need the following attributes:

• 'target' is the target solution folder.
• 'name' should be unique in a solution folder as it is used to generate the file names.
• 'title' and 'description' are copied in the properties file and used in the xaction (they will be displayed when browsing the solutions in pentaho) ; they can also be used the jrxml for the report title band.

Optional attributes are:

• 'template' is the template file used to generate the jrxml (without the .ftl extension), if omitted the default-template from queries will be used
• 'xaction-template' is the template file used to generate the xaction (without the .ftl extension), if omitted the default-xaction-template from queries will be used

Each query must define:

• 'fields' is the list of fields used to perform the query, each field must have the attributes:
• 'name' is the jrxml name, it can be used in the report to retrieve the value with $V{name} ; it is also be used to label the column header
• 'class' is the java type
• 'select' the SQL field : table.field

Optional elements:

• 'where' if given it will be added to the WHERE clause of the report query
• 'groupby' if given it will be added to the GROUP BY clause of the report query
• 'orderby' if given it will be added to the ORDER BY clause of the report query

Additional elements that should be used when using more than of 'field' (they can be used for just one field if the report default values are not suitable):

• 'maincolumnheader' is used as the report main column header textFieldExpression, it must be a valid jrxml expression. For example,

 <![CDATA["My header"]]> or <![CDATA[$R{myHeaderLabel}]]> ...

• 'maincolumncontent' is used as the report main column content textFieldExpression, it must be a valid jrxml. For example:

 <![CDATA[$F{MyField1} + " / " + $F{MyField2} ]]> 

where MyField1 and MyField2 are the names of the 'field' elements of the query ...