Cascading parameters are report parameters that have a hierarchical relationship, as shown in the following three examples:
Product Type
Territory
Mutual Fund Type
Product
Country
Fund Class
City
Fund
In a group of cascading parameters, each report parameter displays a set of values. When the report user selects a value from the top-level parameter, the selected value determines the values that the next parameter displays, and so on.
The advantages of cascading parameters are obvious in comparison with the alternative technique, which is the creation of separate and independent parameters. Consider the Territory-Country-City example. If you create three separate parameters, the territory parameter displays a list of all territories, the country parameter displays all countries, and the city parameter displays all cities. Figure 13‑32 shows independent parameters as they appear to the report user.
The user has to traverse three long lists to select the values, and there is a potential for user errors. The user can inadvertently select invalid combinations, such as Japan, USA, Paris.
Figure 13‑32 Independent parameters
Cascading parameters, on the other hand, display only relevant values based on user selections. For example, the territory parameter displays all the territories, and when the report runs, the user selects a territory, such as NA (North America), then the country parameter displays only countries in the sales territory of North America. Similarly, when the user selects USA, the city parameter displays only cities in the USA. Figure 13‑33 shows cascading parameters as they appear to the report user.
Figure 13‑33 Cascading parameters
Before creating cascading report parameters, you must first create the data set or data sets that return the values to display in the cascading parameter lists. The query in the following example retrieves all the values from the territory, country, and city fields of the Offices table. Specify this query in a data set used to populate the Territory-Country-City parameter lists.
SELECT Offices.territory,
Offices.country,
Offices.city
FROM Offices
In this example, the Territory-Country-City cascading parameter uses a single data set to populate all the parameter lists because the fields are in the same table. If, however, the fields are in different tables or different data sources, you can create multiple data sets, where each data set provides the values for each report parameter in the cascading parameter group. When creating multiple data sets for cascading parameters, you must link the data sets through a common field to create the appropriate dependency relationships.
For example, the data set for the top-level report parameter contains the following query to retrieve all the values from the productline field in the Productlines table:
SELECT Productlines.productline
FROM Productlines
The data set for the second report parameter contains the following query to retrieve the values from the productname field in the Products table. The product name values to get depend on the product line value that the user selects at run time. The WHERE clause gets the value of the product line value at run time.
SELECT Products.productname
FROM Products
WHERE Products.productline = ?
In this second data set, you must also create a data set parameter and bind it to the product line report parameter.
How to create cascading parameters that use a single data set
1 In Data Explorer, right-click Report Parameters, and choose New Cascading Parameter Group.
2 In New Cascading Parameter Group, in Cascading Parameter Group Name, specify a different name if you do not want to use the default name. The name that you specify appears only in the list of report parameters in Data Explorer.
3 In Prompt text, specify the name for the parameter group that appears in the Enter Parameters dialog.
4 Select Single Data Set.
5 Create the report parameters for this group of cascading parameters.
1 In Parameters, choose Add. In Add Cascading Parameter Group, specify the following values:
In Name, type the parameter name.
In Data Set, select the data set that returns the values to populate all the parameter lists.
In Value, select the field that contains the values to pass to the SQL query or filter condition at run time.
In Display Text, optionally select a field that contains the values that you want to display to the user. For example, the values to pass to the SQL query or filter condition are from the productcode field, but you want to display values from the productname field to the user.
Choose OK.
2 In Properties, set the other properties for this report parameter, including the prompt text, display type, and default value.
3 To create the next report parameter, choose Add, and follow the same steps to set up all levels of the cascading parameter group. Figure 13‑34 shows sample values in New Cascading Parameter Group.
Figure 13‑34 A cascading parameter that uses a single data set
6 When you finish creating all the report parameters in the group, choose OK. The cascading parameters appear in Report Parameters in Data Explorer.
How to create cascading parameters that use multiple data sets
This procedure assumes that you created all the data sets that return the values to display in the cascading parameter lists.
1 In Data Explorer, right-click Report Parameters, and choose New Cascading Parameter Group.
2 In New Cascading Parameter Group, in Cascading Parameter Group Name, you can specify a different name. The name that you specify appears only in the list of report parameters in Data Explorer.
3 In Prompt text, specify the name for the parameter group that appears in the Enter Parameters dialog.
4 Select Multiple Data Set.
5 Create the report parameters for this group of cascading parameters.
1 In Parameters, choose Add. In Add Cascading Parameter Group, specify the following values:
In Name, type the parameter name.
In Data Set, select the data set that returns the values to populate the top-level parameter.
In Value, select the field that contains the values to pass to the SQL query or filter condition at run time.
In Display Text, optionally select a field that contains the values that you want to display to the user. For example, the values to pass to the SQL query or filter condition are from the productcode field, but you want to display values from the productname field to the user.
Choose OK.
2 In Properties, set the other properties for this report parameter, including the prompt text, display type, and default value.
3 To create the next report parameter, choose Add, and follow the same steps to set up all the levels in the cascading parameter group.
6 When you finish creating all the report parameters in the group, choose OK. The cascading parameters appear under Report Parameters in Data Explorer.
7 Add data set parameters to the dependent data sets and bind them to the appropriate report parameters from the cascading parameter group.
1 In Data Explorer, right-click the data set that supplies the values for the second parameter in the cascading parameter group, then choose Edit. Edit Data Set displays the query.
2 Add a WHERE clause to the SQL query to filter the values of the data set based on the value of the top-level parameter. Figure 13‑35 shows an example.
Figure 13‑35 Query using WHERE clause to get a value at run time
3 Create a data set parameter to supply a value for the parameter marker in the SQL query.
1 Choose Parameters.
2 Select the placeholder data set parameter, then choose Edit.
3 In Edit Parameter, specify a name for the data set parameter, and link it to the top-level report parameter, as shown in the example in Figure 13‑36.
Figure 13‑36 Linking a data set parameter to a report parameter
4 Choose OK to save the edits to the data set parameter.
4 In Edit Data Set, choose OK to save the changes to the data set.
5 Follow the same steps for subsequent data sets in the cascading parameter group to establish dependencies for all levels in the group.
Figure 13‑37 shows an example of cascading parameters based on the product line and product data sets.
Figure 13‑37 Selecting values for cascading parameters
In Name, type the parameter name.
