Skip to main content
Version Pentaho
Hitachi Vantara Lumada and Pentaho Documentation

Simple OLAP Output Parameterization

  1. Last updated
  2. Save as PDF

This procedure requires a Pentaho Analysis (Mondrian) data source type. Establish this data source and a query before continuing with the instructions below.

You can add dynamic interactivity to a published report such that when a user executes or views it, he can specify how to constrain certain parts of the query data. This is called parameterization. Follow the procedure below to parameterize an OLAP-based report.

  1. Open the report you want to parameterize.
  2. Right-click the Parameters item in the Data pane, then select Add Parameter... from the context menu. The Add Parameter dialogue will appear.
  3. Select or change the options according to the definitions specified in Simple SQL Output Parameterization.
  4. Edit your MDX query and add parameter functions and a where statement, as in the example below. 
    with 
  set [TopSelection] as
  'TopCount(FILTER([Customers].[All Customers].Children,[Measures].[Sales]>0), Parameter("TopCount", NUMERIC, 10, "Number of Customers to show"), [Measures].[Sales])'
  Member [Customers].[All Customers].[Total] as 'Sum([TopSelection])'
  Member [Customers].[All Customers].[Other Customers] as '[Customers].[All Customers] - [Customers].[Total]'
select NON EMPTY {[Measures].[Sales],[Measures].[Quantity] } ON COLUMNS,
  { [TopSelection], [Customers].[All Customers].[Other Customers]} ON ROWS
from [SteelWheelsSales]
where 
(
strToMember(Parameter("sLine", STRING, "[Product].[All Products].[Classic Cars]")), 
strToMember(Parameter("sMarket", STRING, "[Markets].[All Markets].[Japan]")), 
strToMember(Parameter("sYear", STRING, "[Time].[All Years].[2003]"))
)
  5. Click OK to save the query.

    Each parameter must have its own query or data table.

  6. Include the parameterized fields in your report by dragging them onto the canvas.
  7. Publish or preview the report.

When a user runs this report, he will be presented with an interactive field that specifies an adjustable constraint for the column or columns you specified.