Creating Cascading Parameters with Pentaho Report Designer 5.0.1 Stable

Posted on by By Nikhilesh, in Business Intelligence, Open Source Business Intelligence, Pentaho | 0

Here we will be talking about creating cascading parameters with Pentaho Report Designer 5.0.1 Stable.

Cascade parameters provide a way of managing large amounts of data in reports. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter.

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Get your 30 Days Trail Version

PRD cascading parameter

Step-1. Build the Parameter Queries :

I will need to build two parameter queries, the first will need to display a distinct list of countries and the second will need to display a distinct list of cities which belong to the country.

 

To create a new query click on the Add a new query icon (add a new query) and enter a SQL statement which will retrieve a distinct list of countries – make sure you don’t forget to name your query (in this example my country parameter query is named countryList):

PRD cascading parameter 2

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`COUNTRY`
FROM
 `customers`

The next step is to preview the query – as you can see from the screen shot below the countryList parameter query is retrieving a distinct list of countries:

PRD cascading parameter 3

Now we will need to add the second parameter which will display a distinct list of cities based on the value of the country parameter. Close the preview window and click on the icon (add a new query) and enter a SQL statement which will retrieve a distinct list of cities – make sure you don’t forget to name your query (in this example my city parameter query is named cityList):

PRD cascading parameter 4

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`CITY`

FROM
 `customers`

Preview this query. At the moment the above query is only retrieving a distinct list of every city. I need to make sure that this query retrieves a list of cities based on the country parameter

PRD cascading parameter 5

For this query to only show a list of cities based on the value of the country parameter I will need to add the country parameter name to the WHERE clause of the cityList query. As I have not yet created the country or city parameters I will need to make note of the country parameter name I will be specifying in the cityList query – I have chosen to call the country parameter sCountryName. The new cityList query now looks like this:

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Grab The 30 Days Free Trail

PRD cascading parameter 6

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`CITY`
FROM
 `customers`
WHERE
 `customers`.`COUNTRY` = ${sCountryName}

I have now created both parameter queries – I will need to revisit this area later on to modify the report query customerList but for now click on the OK button to close the JDBC Data Source window.

 

Step -2. Create the Parameters

I will need to create two parameters:

  1. A country drop down parameter which is named sCountryName (step 1)
  2. A city drop down parameter which will be named sCityName

Creating the sCountryName Parameter

To create a new parameter make sure you have the Data tab active, right click on the Parameters menu item and select the Add Parameter… option:

PRD cascading parameter 7

An Add Parameter… window will pop up. Before you make any changes to the options expand the connection folder on the left and then select the query which will populate the parameter, in this example it is the countryList query. Below is a screen shot of all the sCountryName parameter options completed:

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Lets Start 30 Days Free Trail

PRD cascading parameter 8

 

The options above are fairly self explanatory however here are descriptions of the most important options:

  • Name: This is the name we specified in step 1, this must match the value of this parameter we put in the cityList WHERE clause
  • Type: The type of this parameter is a drop down
  • Query: The query that will populate this parameter is the countryList query which was created in step 1
  • Value and Display Name: I have set these both to the COUNTRY field as the value is the same as the output I would like displayed in the drop down parameter
  • Value Type: The COUNTRY field is a string
  • Mandatory: I have checked this option as a user must select a country before running the report – this also ensures that the city parameter will be populated

Click the OK button to save the sCountryName parameter.

Creating the sCityName Parameter

The second parameter I will need to create is for the city drop down. There isn’t much different from this parameter and the sCountryName parameter so I can copy and paste this parameter and then change some options.

To copy the parameter right click on the sCountryName parameter under the Data tab and select the Copy option (alternatively you can use the CTRL+C shortcut):

PRD cascading parameter 9

To paste the parameter right click on the Parameters… item and select the Paste option (alternatively you can use the CTRL+V shortcut):

PRD cascading parameter 10

This will create an identical copy of the sCountryName parameter.

PRD cascading parameter 11

 

Double click on the sCountryName parameter which is located at the bottom of the Parameters list, this will open the Edit Parameter… window. Before you make any changes to the options expand the connection folder on the left and then select the query which will populate this parameter, in this example it is the cityList query. Below is a screen shot of all the sCityName parameter options completed:

PRD cascading parameter 12

The options above are fairly self explanatory however here are descriptions of the most important options:

  • Name: I have decided to name this parameter sCityName
  • Type: The type of this parameter is a drop down
  • Query: The query that will populate this parameter is the cityList query which was created in step 1
  • Value and Display Name: I have set these both to the CITY field as the value is the same as the output I would like displayed in the drop down parameter
  • Value Type: The CITY field is a string
  • Mandatory: I have checked this option as a user must select a city before running the report

Step-3. Modify the Report Query

Now that the parameters and parameter queries have been created we are now ready to modify the report query (customerList) to use the new parameters. To modify the report query expand the data source connections under the Data tab and double click on the report query, customerList:

PRD cascading parameter 13

This will open the JDBC Data Source window and automatically highlight the customerList query:

PRD cascading parameter 14

To make the customerList query use the two new parameters created in step 2 you will need to add in two new conditions to the WHERE clause like:

PRD cascading parameter 15

 

Here is the above query in a format which you can copy and paste from below:

SELECT
 `customers`.`CUSTOMERNUMBER`,
 `customers`.`CUSTOMERNAME`,
 `customers`.`CITY`,
 `customers`.`COUNTRY`

FROM
 `customers`
WHERE
 `customers`.`COUNTRY` = ${sCountryName}
AND
 `customers`.`CITY`    = ${sCityName}

Now the report query will use the values of the sCountryName and sCityName parameters in its WHERE clause. Click on the OK button to close the JDBC Data Source window.

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 alternatively click on View > Preview menu item.

PRD cascading parameter 16

By default nothing will be displayed in the report and none of the drop down parameters will be populated (if you would like a country to be set when you first run your report set a value for the Default Value option in step 2 for the sCountryName parameter).

To check if the cascading parameters are working pick a country from the first drop down for example Australia:

PRD cascading parameter 17

 

The city drop down parameter should automatically populate with the cities which belong to Australia (even though I’m not sure if you would classify Glen Waverly as a city):

PRD cascading parameter 18

After selecting a city i.e. Melbourne click on the UPDATE button and now the report is filtered to customers who are located in the country Australia and the city Melbourne:

PRD cascading parameter 19

 

Thanks

Rohit Verma

Helical IT Solutions

logo

Best Open Source Business Intelligence Software Helical Insight is Here

logo

A Business Intelligence Framework

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments