Skip to main content
Pentaho Documentation

Output parameterization

Parent aricle

You can create reports with parameters that the report reader can adjust, which is easier than creating multiple reports with the same basic layout and similar data. Simple parameterization involves changing data values. For example, report readers might want to filter data by product. You can provide report readers with the option to filter by the values in a drop-down list. In this case, you would parameterize the column that contains product names, so report readers can change for which product they want to see data.

While simple parameters are added after the data structure has been defined through a query, advanced parameters give readers the power to change the data structure itself. For example, you might offer an option to select among multiple columns in a given table. These parameters must be expressed as formulas and are executed with the query.

Simple SQL output parameterization

You can add dynamic interactivity to a published report such that when you execute or view it, you can specify how to constrain certain parts of the query data. This is called parameterization. This procedure requires a JDBC data source type.
NoteYou can only use this procedure to parameterize data returned by a query. You cannot use a WHERE statement to dynamically choose columns or change the structure of tabular data. If you need to go beyond the capabilities of the method explained in this section, see Advanced SQL output parameterization to create a custom formula instead.

Perform the following steps to parameterize a report by adding an SQL WHERE statement to your query.

Procedure

  1. Open the report you want to parameterize.

  2. Click the Data tab in the upper right pane.

  3. Right-click the Parameters item in the Data pane, then select Add Parameter from the context menu.

    The Add Parameter dialog box appears.Add Parameter dialog
  4. The table below describes each of the options for configuring the parameters:

    FieldPurpose
    NameThe name of the parameter in Report Designer.
    LabelThe label of the parameter that is shown to report readers.
    Label formulaA formula that dynamically changes the name of the parameter on the report.
    Value TypeThe data type of the column you selected in the Value field.
    Data FormatDetermines how the data specified by the Value Type is formatted. This field is especially useful when formatting dates and timestamps.
    Data Format formulaA formula that dynamically changes the value of the Data Format.
    Default ValueThe default value for the parameter. For a parameter with multiple values, the values are specified as arrays.
    Default Value FormulaA formula that dynamically changes the Default Value or values.
    Post-Processing FormulaA formula that updates a selected parameter. This formula is executed when a parameter is submitted, and can be used to validate parameter input. For example, you can change all text to be upper case.
    MandatorySpecifies whether this parameter is required to display any data in the report.
    HiddenSpecifies whether to hide the parameter from displaying when the value is already passed in a session variable. This option can be used in combination with the Post-Processing Formula option to create a calculated parameter.
    Hidden formulaA formula to hide a parameter when the formula evaluates to TRUE. If left blank, the selected parameter is hidden when the Hidden check box is selected.
    Display TypeThe parameter type.
    QueryA list of queries that you have already defined. Use the toolbar above the left pane to define a new query.
    ValueField in the data source that is substituted in the query.
    Display Value FormulaA formula that changes the contents inside the list or drop-down menu in the report.
  5. Edit your target data source by double-clicking its entry in the Structure pane.

  6. Below your FROM statement, add a WHERE statement that specifies which column you would like to query the user about. Assign it to a parameter that has a name descriptive enough for users to understand.

    This column should be one of the columns you have a SELECT statement for in the same query.
  7. Click OK to save the query.

  8. Include the parameterized fields in your report by dragging them onto the canvas.

  9. Publish or preview your report.

Results

When you run this report, you are presented with an interactive field that specifies an adjustable constraint for the column you specified.
In the example below, the constraint would be a specific product line from the PRODUCTLINE column of the PRODUCTS table.
SELECT
                PRODUCTLINE,
                PRODUCTVENDOR,
                PRODUCTCODE,
                PRODUCTNAME,
                PRODUCTSCALE,
                PRODUCTDESCRIPTION,
                QUANTITYINSTOCK,
                BUYPRICE,
                MSRP
FROM
                PRODUCTS
WHERE PRODUCTLINE = ${ENTER_PRODUCTLINE}
ORDER BY
                PRODUCTLINE ASC,
                PRODUCTVENDOR ASC,
                PRODUCTCODE ASC

Advanced SQL output parameterization

You can add dynamic interactivity to a published report so when you execute or view it, you can specify how to constrain specific parts of the query data. This process is called parameterization.

This procedure requires a JDBC (Custom) data source type. You must establish this data source before continuing with the instructions below. You do not need to construct a query yet.

NoteThis option allows you to parameterize both structure and values. If you only need to parameterize values, see Simple SQL output parameterization instead.

Perform the following steps to parameterize a report by creating a custom formula.

Procedure

  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 dialog box appears.
  3. Select or change the options according to the definitions specified in Simple SQL Output Parameterization.

  4. Go to the Structure pane, then select Master Report.

  5. In the Attributes pane, click the round green Plus Sign (+) in the name field of the Query section.

    The Expression window appears.
  6. Click the Ellipses button .

    The Formula Editor appears.
  7. In the Formula field, use a SELECT DISTINCT statement to parameterize the data structure with your previously defined parameter, as shown in the example below.

    ="SELECT DISTINCT " & [paramexample] & " AS COL1 FROM PRODUCTS"
    The paramexample is a placeholder for the name of the parameter you created earlier. COL1 is the example name of the element to be parameterized in your report, and PRODUCTS is an example table name in your database.
    NoteThe spaces after DISTINCT and before AS are important. Do not omit them.
  8. Click OK when you are done with the query, then click Close in the Expression window.

  9. Add a field of the applicable data type to your report, and name it according to the AS statement you defined in your query.

    In the example above, the name of the text field would be COL1.
  10. Publish or preview the report.

Results

When you run this report, you are presented with an interactive field that specifies the source of the column you specified.

Simple metadata output parameterization

You can add dynamic interactivity to a published report so when you execute or view it, you can specify how to constrain specific parts of the query data. This process is called parameterization.

This procedure requires a metadata data source type. You must stablish this data source and a query before continuing with the instructions below.

Perform the following steps to parameterize a metadata-based report.

Procedure

  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 dialog box appears.
  3. Select or change the options according to the definitions specified in Simple SQL Output Parameterization.

  4. Edit your query and add the columns you want to parameterize to the Conditions field.

  5. Create a parameter token in the Value field of each row in the Conditions area, and a valid default value in the Default field.

    Parameter tokens are in braces {} and do not contain spaces.
  6. Click OK to save the query.

  7. Include the parameterized fields in your report by moving them onto the canvas.

  8. Publish or preview the report.

Results

When you run this report, you are presented with an interactive field that specifies an adjustable constraint for the column or columns you specified.

Simple OLAP output parameterization

You can add dynamic interactivity to a published report so when you execute or view it, you can specify how to constrain certain parts of the query data. This process is called parameterization.

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

Perform the following steps to parameterize an OLAP-based report.

Procedure

  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 dialog box appears.
  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.

    NoteEach parameter must have its own query or data table.
  6. Include the parameterized fields in your report by moving them onto the canvas.

  7. Publish or preview the report.

Results

When you run this report, you are presented with an interactive field that specifies an adjustable constraint for the column or columns you specified.