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

Simple SQL Output Parameterization

  1. Last updated
  2. Save as PDF

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