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

Output Parameterization

  1. Last updated
  2. Save as PDF

You can create reports with parameters that the person viewing the report can adjust, which is easier than creating multiple reports with the same basic layout and similar data.

Simple parameterization involves changing data values. For instance, you might give readers an option to filter by the values in a drop-down list. This can include values stored in a particular column; for instance, you could choose to parameterize a column that contains product names, in which case the report reader would be able to change which product he wanted to see data for. Simple parameters are added after the data structure has been defined through a query.

Advanced parameters give readers the power to change the structure of the data. For instance, you might offer an option to select among multiple columns in a given table. These parameters must be expressed as formulas, and are executed along with the query.

Simple SQL Output Parameterization

This procedure requires a JDBC data source type.

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 a report by adding an SQL WHERE statement to your query.

You 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.

  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 will appear.
  4. Select or change the options according to the definitions below:
    Field Purpose
    Name The name of the parameter within Report Designer
    Label The label of the parameter that will be shown to report readers -- a "friendly name"
    Value Type The data type of the column you chose in the Value field above
    Data Format Determines how the data specified by Value Type is formatted. For instance, dates and times can be formatted in a variety of different ways
    Default Value The value from the Value column that you want to pre-populate the parameter object with. This is a text field, so you must know the values in advance
    Default Value Formula Allows the Default Value to change dynamically, based on the formula you specify
    Post-Processing Formula Allows you to update a selected value according to conditions you specify in your formula
    Mandatory A checkbox which determines whether this parameter is required in order to display any data in the report
    Hidden A checkbox which hides the parameter from appearing when the value is already passed in a session variable
    Display Type The method of selection for this parameter; determines how report readers choose different values
    Query A drop-down list of queries that you have already defined. If you need to define a new query, use the toolbar above the left pane
    Value The value that is substituted into the query
    Display Value Formula Forces the display value to change depending on the conditions specified in your formula
  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, assigned to a parameter that has a name descriptive enough for users to understand. This 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.

When a user runs this report, he will be presented with an interactive field that specifies an adjustable constraint for the column you specified. For instance, 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

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

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 a report by creating a custom formula.

This option allows you to parameterize both structure and values. If you only need to parameterize values, see Simple SQL Output Parameterization instead.

  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 will appear.
  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 + icon in the name field of the Query section. The Expression window will appear.
  6. Click [...]. The Formula Editor will appear.
  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 (paramexample is a placeholder for the name of the parameter you created earlier, COL1 is the example name of the element in your report that will be parameterized, and PRODUCTS is an example table name in your database).

    The spaces after DISTINCT and before AS are extremely important. Do not omit them.

    ="SELECT DISTINCT " & [paramexample] & " AS COL1 FROM PRODUCTS"
  8. Click OK when you are done with the query, then click Close in the Expression window.
  9. Add a field of the appropriate 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.

When a user runs this report, he will be presented with an interactive field that specifies the source of the column you specified.

Simple Metadata Output Parameterization

This procedure requires a Metadata 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 a Metadata-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 dialog box will appear.
  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 dragging them onto the canvas.
  8. 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.

Simple OLAP Output Parameterization

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