Skip to main content
Pentaho Documentation

Add Parameters to Your Report

Previously, you added a table and a chart to your report. Now, you will make your report interactive by setting parameters. When you set parameters, users are prompted for a value or values when they run the report.

  1. In the Report Designer, if it is not already open, click File > Open and select to open your Orders report.
  2. In the menu bar, go to Data > Add Parameter. Alternatively, you can click File:/rd_add_parameter.png (Master Report Parameter) under the Data Tab in the Report Designer workspace. The Add Parameter dialog box appears.
  3. In the Add Parameter dialog box, enter enter_prodline in the Name text field.
  4. Enter Select Line in the Label text field.
  5. Next to Display Type, select Drop Down so users can select a product line.
  6. Click on JDBC (SampleData (Hypersonic) under DataSources, and then click File:/edit.png (Edit) to add a query that supplies the values, (motorcycles, cars, ships, and so on), from which users of the report must choose. The JDBC Data Source dialog box appears.
  7. Under Connections, select SampleData (Memory).
  8. Next to Available Queries click File:/add.png (Add). A new query placeholder is added to the list (Query 2).
  9. In the Query Name text field, enter prodlineList.
  10. Enter your SQL query in the Query box. Either copy and paste the following SQL statements directly under Query in the Static Query tab:
    SELECT DISTINCT
         "PRODUCTS"."PRODUCTLINE"
    FROM
         "PRODUCTS"

    By entering these lines, report users see a prompt when they open the report in the Pentaho User Console that allows them to enter a product line. That way, they can examine orders by product line. If you do not add the lines, the report displays orders for all product lines.

    Or, use the SQL Query Designer to build your query as shown in the following steps:
    Step Description
    1 Click File:/edit.png (the Edit icon) to the upper right of the State Query tab.
    2 In schema filter menu of the SQL Query Designer, select PUBLIC.
    3 Double-click the PRODUCTS table to select it.
    4 In the right panel, click PRODUCTS and choose Deselect All.
    5 Right-click SELECT in the upper left panel and choose Distinct.
    6 In the right panel, select PRODUCTLINE.
    7 Click OK to exit the SQL Query Designer and go to Step 11.
  11. Click OK to exit Data Source dialog box.
  12. In the Add Parameter dialog box under DataSources, select prodlineList.
  13. Next to Value Type, select String.
  14. Optionally, type a default value, for example, 'Motorcycles', in the Default Value text box as shown in the following example:Add Parameter Dialog Box in the Report Designer
  15. Click OK to exit the Add Parameter dialog box.
  16. Now that you have created a product line parameter, you must map it back to your query (Query 1). Under Data, double-click Query 1. to open Query 1 in the JDBC Data Source dialog box.
  17. Click File:/edit.png (Edit) to the upper right of the State Query tab to access the SQL Query Designer, right-click PRODUCTLINE in the right panel, and then select add where condition. The condition.edit dialog box appears.
  18. Type ${enter_prodline} into the edit area in the lower panel of the dialog box as shown in the following example, and then click OK:
    File:/rd_condition_edit.png
  19. Click OK to exit the SQL Query Designer.
  20. Click OK to exit the Data Source dialog box.
  21. Click File:/preview_eye.png (Preview) and notice the new product line menu as shown in the following figure:Product Line Parameter in a Report

  22. Save and close the report.

You are now ready to publish your report