Add parameters to your report

Last updated
Save as PDF

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.

Procedure

In the Report Designer, if it is not already open, click File Open and select to open your Orders report.
In the menu bar, go to Data Add Parameter. Alternatively, you can click Master Report Parameter () under the Data Tab in the Report Designer workspace.
The Add Parameter dialog box appears.
In the Add Parameter dialog box, enter enter_prodline in the Name text field.
Enter Select Line in the Label text field.
Next to Display Type, select Drop Down so users can select a product line.
Click on JDBC (SampleData (Hypersonic) under DataSources, and then click Edit icon () 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.
Under Connections, select SampleData (Memory).
Next to Available Queries click Add icon ().
A new query placeholder is added to the list (Query 2).
In the Query Name text field, enter prodlineList.
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 users to enter a product line. That way, users 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:
1. Click Edit icon () to the upper right of the State Query tab.
2. In the 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 the next step.
Click OK to exit Data Source dialog box.
In the Add Parameter dialog box under DataSources, select prodlineList.
Next to Value Type, select String.
Optionally, type a default value (for example, Motorcycles) in the Default Value text box, as shown in the following example:
Click OK to exit the Add Parameter dialog box.
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.
Click Edit icon () 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.
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:
Click OK to exit the SQL Query Designer.
Click OK to exit the Data Source dialog box.
Click the Preview button () and notice the new product line menu, as shown below:
Save and close the report.

Results

You are now ready to publish your report.