Advanced SQL output parameterization

Pentaho Report Designer
Version
10.0.x
Audience
anonymous
Part Number
MK-95PDIA008-05
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.

Note: This 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.

  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.
    Note: The 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.
When you run this report, you are presented with an interactive field that specifies the source of the column you specified.