Background:

Cascading parameters offer users the ability to filter data based on multiple criteria, significantly improving the efficiency of their search process. This feature is commonly employed in business intelligence and analytics applications.

This article presents a comprehensive guide on creating and configuring cascading parameters within reports with data sets. By following the outlined steps to create a cascading parameter to look up a country and then a city, users will gain the necessary expertise to effectively leverage cascading parameters in their reporting workflows.

In this example, "Country" will serve as the parent parameter, while "City" will act as the child parameter.

Steps to Complete:

1. Create a cached or direct dataset that includes the fields you wish to use as nested parameters. Save and exit. This dataset will act as the “parent” dataset.

2. Duplicate the parent dataset and edit this new dataset. This dataset will be referred to as the “child” dataset.

3. Remove all the fields from the child dataset, except the fields that are necessary for the parent and child parameters. In this example, these fields that are kept are Country and City.

4. On the child dataset, add a parameter that is based on User Input.
   

5. Add a filter to this dataset that uses the new parameter. Set it to the field of the first nested parameter you want to use. In this example, that would be the Country field. Save and exit this dataset.
   

6. Create a new report and add both the parent and child datasets. You will notice Parameter1 from step 4 is automatically added. Do not change any settings on it for now.

7. Create two new parameters on the report. The first parameter is the parent field and the second parameter is the child field. The new parameters in this example are named “CountryParameter” and “CityParameter.”

8. On the parent parameter “CountryParameter,” bind to the parent dataset and set the available values to the appropriate field.
   

9. On the child parameter “CityParameter,” bind to the child dataset and set the available values to the appropriate field.
   

10. On the Parameter1 that came from the child dataset, change the default value to a non-queried value, and within that value, use the parent parameter by selecting the expression editor next to the empty field. This will allow the child dataset to be filtered by the parent parameter’s selection. Also set Hidden to True.
    

11. Now bind data from the parent dataset to a table in the report and add a filter to this table.  The filter should go against the field of the child parameter, as shown below. If you are using a Multi-Value parameter, you will need to switch the "=" operator to an "IN" operator.
    

12. Now save and preview the report. Notice that the child parameter cannot be selected until the parent parameter has been selected.