05. AdvancedMQLQuery Example

The AdvancedMQLQuery extension of MQLQuery contains two additional features in addition to the core set of features within MQL:

  • The ability to alias selections, so the same business column can be selected twice with two different join paths
  • The ability to specify calculated selections

These features are expressed via the AdvancedMQLQuery API in both Java and XML.

Aliased Selections

To specify an aliased selection in XML, add an additional element entitled "alias":

<selection>
    <alias>Alias1</alias>
    <view>bc_ORDERS</view>
    <column>BC_ORDERS_ORDERNUMBER</column>
</selection>

Once specified in a selection, the same alias path can be referenced in an MQL constraint instead of the traditional category name:

<constraint>
    <operator>AND</operator>
    <condition>[Alias1.BC_ORDERS_ORDERDATE] &gt;= DATE(1;1;2007)</condition>
</constraint>

Selection Formulas 

To specify a selection formula in XML, use the following syntax in the selections XML:

<selection>
    <formula>SUM([bc_ORDERS.BC_ORDERS_ORDERNUMBER])</formula>
</selection>

 Using AdvancedMQLQuery functionality within Pentaho's BI Platform

In order to take advantage of this example MQL functionality, an additional component definition is required within the MQLRelationalDataComponent component.  The class of the MQLQuery must be specified as seen below:

<component-definition>
    ...
    <MQLQueryClassName>org.pentaho.pms.example.AdvancedMQLQuery</MQLQueryClassName>
</component-definition>