Analyse This

With the release of Pentaho Report Designer 3.5 users can create parameters through the designer rather than having to build a xaction which at times could be messy and confusing. Here is a tutorial on how you can create a basic parameter with Pentaho Report Designer 3.5.

If you aren’t already aware Doug Moran (Pentaho’s Community Leader) has already created a set off screen casts showing off features of Pentaho Report Designer 3.5.

The Scenario

In this tutorial I already have a report which is using the Steel Wheels sample data. My current report displays all customers by the country they are located in and I would like to create a parameter which will filter the results to a user specified country.

Below is a screen shot of my initial report structure and preview screen:

Step 1. Build the Parameter Query

The first step is to build a query which will display a unique list of countries. To get started I will need to add another query to the data source which I created to connect to the sample data, from the screenshot below you can see I already have a query named allCustomers which my current report uses to. To create a new query click on the icon (add a new query) and enter a SQL statement which will retrieve a distinct list of countries also don’t forget to name your query – in this example it is allCustomerCountries:

Make sure you give meaningful names to your queries as you will always need to reuse them through throughout your report.

Just to be sure preview the report, as you can see from the screen shot below, the allCustomerCountries query returns a unique list of countries. Close the Preview window and then click OK to save your new query and close the Data Sources window.

Step 2. Create the Parameter

To create a parameter make sure you have the Data tab active. Right click on the Parameters menu item and select the Add Parameter… option.

This will display an Add Parameter… window with a set off options which will need to be filled out (as you can see in the screenshot below I have already filled out the options):

Here is a description of each option available from the Add Parameter… window:

• Name : The name of your parameter, this will be displayed under the Parameters menu item – this is important so make sure you give the parameter a meaningful name
• Label : The label which will be displayed to the user, for example “Select a country”
• Type : The type of parameter, for example “Drop Down”
• Query : The query which contains the contents of the parameter, for example customerCountryList
• Value : The actual value of the parameter – this isn’t displayed to the user and is used in the report query, for example Product Number instead of Product Name
• Display Name : The value which will be displayed the the user, for example Product Name
• Value Type : The data type of the parameter, for example String, Integer, Date etc.
• Default Value : The default value which is to be selected when the report is opened, this should be equivalent to the Value option, for example Australia
• Mandatory : Check this option if the parameter must be used to display results

With the options I have filled out I’m trying to create a report which:

• Uses a drop down parameter type
• Displays the text “Select a country” next to the drop down
• By default select Australia as the country in the drop down and in the report results
• Make sure that this parameter is always used to display report results

Click OK to save your new parameter.

Step 3. Modify the Report Query

Now that we have created our parameter we have to tell the report query to use the parameter’s value in its WHERE clause. To edit the report query, double click on the query under the Data tab, in this example the original report query was named customerCountries and looks like the screen shot seen below:

To make sure the report query will use our new parameter (enterCountries) add in the following piece of code to the WHERE clause:

As you can see the we have made the WHERE clause for the customer country equal to the parameter name enterCountry which is wrapped between a ${}.

At all times parameter names must be wrapper between ${}

Click OK to save and close the report query, customerCountries.

Step 4. Preview the Report

The last step is to preview the report, to do this you can click on the (preview icon) found in the top left hand corner or click on View > Preview menu item.

As you can see the parameter is set to a default value of Australia and therefore every time the report is run it will be filtered to Asutralia. To test if the report parameter is working I selected a new country Norway and clicked the Update button (if you don’t want to keep clicking Update after you choose a new parameter check the Autocomplete on selection check box).

Step 5. Publish your Report

The final step is to publish your report to Pentaho Business Intelligence Server (click here to learn how to publish reports) where you will see that the parameter looks exactly the same to when it was previewed in the designer:

Downloads

Below is a link to download the Pentaho Report file for this tutorial.

That is how you can create a basic parameter driven report using Pentaho Report Designer 3.5.

Enjoy.