View source | Discuss this page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Add page
Show collection (0 pages)
Collections help

Search

Modules:Analytics Widgets

Contents

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:


Client analytics process example.png


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:


Client analytics process setup.png

Cube Widget

Openbravo Analytics can also be used to define a widget:


Client analytics widget example.png


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


Client analytics widget setup.png

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:

Retrieved from "http://wiki.openbravo.com/wiki/Modules:Analytics_Widgets"

This page has been accessed 3,725 times. This page was last modified on 14 May 2017, at 15:12. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.