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

Advanced SQL Output Parameterization

  1. Last updated
  2. Save as PDF

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