Analyse This

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.

In this example I will be using the Steel Wheels sample data to create a report which will display a list of customers using two parameters: a drop down parameter which will contain a distinct list of countries which will then filter another drop down parameter which contains cities which belong to the country.

The Scenario

The current report displays customers information (includes the customer number, name, city and country). I would like to create parameters which will enable users to filter the list of customers by a country and then the cities which belong to that country.

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

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 get started I will need to add a new query to the data source which I created to connect to the sample data, from the screen shot below you can see I already have a query named customerList which the current report uses to display customer information.

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 – make sure you don’t forget to name your query (in this example my country parameter query is named countryList):

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:

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):

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:

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:

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:

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:

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):

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

This will create an identical copy of the sCountryName parameter.

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:

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:

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

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:

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

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.

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:

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):

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:

Downloads

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

Final Notes

This was tested with PRD 3.5 and should work with PRD 3.6. If you are looking to eliminate the cascading feature you will need to remove the WHERE clause from the cityList parameter query: