Customizing your Pentaho Metadata Query in Pentaho Reporting

May 12, 2011
Submitted by Will Gorman, Pentaho Team Developer

Are you using Pentaho Metadata, and have reached the limits of what you can do with the Metadata Query Editor in Report Designer?  Would you like to dynamically build constraints or parameterize the sort order of columns?  Starting in Pentaho Reporting 3.8, these things are possible if you use the Pentaho Metadata (Custom) datasource.

Here's a step by step guide to building a report using the custom datasource:

1. Use the regular pentaho metadata datasource to build an MQL Query
   1. Launch Pentaho Report Designer
   2. Create a new Report
   3. Add a Metadata dataset to the report, select the steel-wheels/metadata.xmi file in the bi-server/pentaho-solutions folder. Set your domain name to "steel-wheels". Note that the domain name should match the domain name of the bi server for publishing purposes.
   4. Define a query using the Metadata Query Editor, select Territory for both selections and sorting
   5. copy the MQL XML from the Metadata dataset dialog
   6. Once you've copied the XML, save the query.  You will use this during design time to build a report.
2. Create a Metadata (custom) dataset for the report
   1. Add the Metadata (custom) dataset to the report.  Select the metadata.xmi file, and specify the same domain name as above.  The Metadata (Custom) dataset does not define queries like the Metadata dataset.  Instead, it executes the custom query you define in the report.
3. Specify a custom MQL query in the report
   1. Select the Structure Tab, followed by the Master Report item in the tree
   2. Select the Attributes tab for the Master Report
   3. Click on  the query name formula button, which is a green plus sign, and then click on the [...] button in the Edit Expression Dialog. This brings up the formula editor.
   4. Paste the MQL from step 1 into the formula editor.  Notice that there is a formula parsing error.

      <?xml version="1.0" encoding="UTF-8"?>
      <mql><domain_id>steel-wheels</domain_id><model_id>BV_HUMAN_RESOURCES</model_id>
      <options><disable_distinct>false</disable_distinct></options>
      <selections><selection><view>BC_OFFICES_</view><column>BC_OFFICES_TERRITORY</column>
      <aggregation>NONE</aggregation></selection></selections>
      <constraints/>
      <orders><order><direction>ASC</direction><view_id>BC_OFFICES_</view_id>
      <column_id>BC_OFFICES_TERRITORY</column_id></order></orders>
      </mql>
      

   5. Add =" to the beginning of the XML, and " to the end.  Replace the quotes in the XML with two quotes (""), and make sure there are no new lines in the formula.  The parsing error icon will go away once you've defined the query correctly as a static formula string.  Here is a simple example:="<?xml version=""1.0"" encoding=""UTF-8""?><mql/>"
      
4. Add a text-field to the report
   1. Drag and drop the Territory field  from the first datasource into the details band of the report
   2. Preview the Report!  You should see a list of territories.

That's great, but why not just use the Metadata dataset to do that?  Now we'll take our report and do some rich parameterization of the MQL XML.  This is not possible with the standard Metadata dataset.

1. Define a report parameter called "Sort"
   1. Select the Data tab, right click on Parameters, and select "Add Parameter...".
   2. Define a Table dataset that contains a display and value column, with ASC and DESC for the values.
   3. Choose DropDown for display type and enter in the display and value columns.  Note that right now, due to some issues in the parameter dialog, you may see exceptions when defining the new dataset, and you may not be able to see the display and value column in the Display and Value dropdowns in the dialog.
2. Parameterize the MQL Query with the sort parameter
   1. In the formula editor, splice in a value for the direction XML value:

       <direction>" & [Sort] & "</direction>
      

   2. apply the formula changes and render the report!

Congratulations!  Now you can select "ASC" and "DESC" in the Sort parameter drop down, and see the report values order differently.  You can download the complete PRPT example here:

custom.prpt

Are you interested in how this all works?  Here are some details.  Normally, a dataset, also known as a DataFactory, defines a set of named queries that can be executed by a report.  The downside to these named queries is that they aren't customizable.  The Metadata (Custom) and Advanced -> JDBC (Custom) datasets skip the named queries, and execute the query passed into the dataset directly.  This allows the developer to fully customize the query before execution using the formula editor, as demonstrated above. 

The report engine first asks the standard datasets that contain named queries, such as "Query 1", if they can execute the custom MQL XML or SQL.  If not, then the query name is passed in to a custom dataset.  Note that you can only have one custom dataset defined a in report.  If you'd like to use more than one custom dataset in a report, you can include subreports that each define a custom dataset.

Also note that a defined formula takes precedence over an attribute value, this allows the report designer to display the regular metadata dataset in design time for field selection, but then switch to the formula during runtime.

To learn more about MQL Query Syntax, please see 03. Pentaho Metadata MQL Schema