Modules:Analytics Widgets

Contents 1 Using Cube Queries in Process Windows and Widgets 2 Cube Queries in Process Windows 3 Cube Widget 4 Parameterized Queries 4.1 Property Path Parameters 4.2 Date Parameters 4.3 Standard Parameters

Using Cube Queries in Process Windows and Widgets

Openbravo Analytics queries can be used in both process windows as well as widgets. An important feature is that the queries can be parameterized. So the MDX query can be defined with parameters which are substituted with parameters set in the process window or the widget.

Note: you don't need to manually type the queries you want to use in widgets/process definitions, they can be created in the generic OB Analytics window, then saved, and then copied from the 'Analytics Query' window to the widget or process definition.

Cube Queries in Process Windows

A process is a powerfull way to control and display analytics queries. You can make use of the flexibility of defining parameters in the process and then use these parameters in the MDX.

Here is an example of a process window showing multiple analytics results in multiple tabs:

This process is setup by setting the report queries in the process window and using the 'org.openbravo.client.analytics.report.AnalyticsProcessReportHandler' as the report handler:

Cube Widget

Openbravo Analytics can also be used to define a widget:

To create a new widget set the following information in the widget record:

• the widget superclass should be the 'Analytics Widget'
• Set a query in the Analytics child window, the query can be copied directly from the 'Analytics Query' window. You can substitute parameters of the widget in the query (see next sections)

Parameterized Queries

In the Saiku XML in widgets and process definitions it is possible to use parameters defined in the process/widget. The parameters are specified in the form ${parameterName.property} in the Saiku XML.

<MDX>SELECT
NON EMPTY {[Measures].[orderedQuantity]} ON COLUMNS,
NON EMPTY {Hierarchize({[salesOrder-businessPartnerCategory].[businessPartnerLevel].Members})} ON ROWS
FROM [Purchase Orders]
WHERE CrossJoin({[salesOrder-orderDate].[${year}]}, {[salesOrder-organization].[${organization.id}]})</MDX>

See the part ${organization.id} above, this refers to the organization parameter which corresponds to an Organization object. From the Organization object the id is substituted in the MDX.

Property Path Parameters

The example above retrieves the id of the Organization object, but you can also 'walk' through the model and use values of referenced objects. For example this parameter definition will also work fine: ${organization.client.id}

Date Parameters

If a property resolves to a date then the date parameter can be further qualified to YEAR, MONTH, DAY, QUARTER, DATE. The DATE is a special value in the format: yyyyMMdd, 20141107 for example.

For example see the [salesOrder-accountingDate].[${year}] section in the Saiku XML below:

<?xml version="1.0" encoding="UTF-8"?>
<Query name="BF868C59-7FDF-E82F-8106-CEBC4178C56F" type="QM" connection="Openbravo" cube="[Sales Orders]" catalog="Openbravo" schema="Openbravo">
  <MDX>SELECT
NON EMPTY Hierarchize(Union(CrossJoin({[salesOrder-organization].[${organization.id}]}, 
CrossJoin({[salesOrder-accountingDate].[${year}]}, {[Measures].[orderedQuantity]})), CrossJoin({[salesOrder-organization].[${organization.id}]}, 
CrossJoin({[salesOrder-accountingDate].[2013]}, {[Measures].[lineNetAmount]})))) ON COLUMNS,
NON EMPTY {Hierarchize({{[product-productCategory].[All product-productCategorys], [product-productCategory].[All product-productCategorys].Children}})} ON ROWS
FROM [Sales Orders]</MDX></Query>

The DATE format for an input parameter can be used in combination with the DayFilter hierarchy in a date dimension. For example see this xml:

<?xml version="1.0" encoding="UTF-8"?>
      <Axis location="FILTER" nonEmpty="false" filterCondition="[orderDate.DayFilter_orderDate].[DateValue].currentMember in {[orderDate.DayFilter_orderDate].[${dateFrom.DATE}]:[orderDate.DayFilter_orderDate].[${dateTo.DATE}]}">
        <Dimensions>
          <Dimension name="currency">
            <Inclusions>
              <Selection dimension="currency" type="member" node="[currency].[${currency.id}]" operator="MEMBER" />
            </Inclusions>
            <Exclusions />
          </Dimension>
          <Dimension name="orderDate">
            <Inclusions>
              <Selection dimension="orderDate" type="level" node="[orderDate.DayFilter_orderDate].[DateValue]" operator="MEMBERS" />
            </Inclusions>
            <Exclusions />
          </Dimension>
        </Dimensions>
      </Axis>

Standard Parameters

Openbravo Analytics allows you to use specific standard parameters in the Saiku XML:

• currentClient: for example ${currentClient.id}
• currentOrganization: for example ${currentOrganization.id}
• currentUser: for example ${currentUser.name}
• currentDate: for example ${currentDate.YEAR} or ${currentDate.MONTH}

Category: Analytics