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 embedded 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 new report, either in RDL or Blank page format, and add a data set with Country and City values. This dataset will act as the parent dataset.

2. Add two new parameters, such as "Country" and "City." The available values for these parameters should not be set at this stage.

3. Edit the data set and add parameters that are linked to the parameters created in step 2. For instance, the parent Parameter name is “Country”, and the value is {@Country}, while the child parameter name is “City”, and the value is {@City} (refer to the image below).

4. Implement two filters for each of the parameters. The left side of the filter should display the value from the dataset, while the right side should correspond to the matching parameter. The operator used between the two sides of the filter should be "=" for single value parameters and "[]" for multi-value parameters. Save and exit.

5. Create a new dataset based on the same data source used in step 1, utilizing the identical query as the previously created dataset. Name it so you know it is the parent level parameter (Country) that will be working here. Save and exit.

6. Create another dataset for the child, once again using the same data source and query as the dataset from step 1 and step 5. Name this dataset and save it. This example is named “CityLookup.” Do not close the dataset designer yet.

7. In the dataset designer of CityLookup, add a parameter to the parameters section, where the value will be the parent level parameter from step 3. In this example, the parent level parameter is "Country," while the child parameter is "City." Therefore, the value to be entered in this step is "{@Country}" and the name of this parameter as "SelectedCountry" (refer to the image below).

8. Apply a filter to the dataset. Select the parent level value on the left side, set the comparison operator as "=" for single value and "[]" for multi-value, and use the parent parameter created at the report level as the value on the right side. 

9. Revisit the parameters created in step 2. Connect the available values and their corresponding data sets. For example, the parent parameter "Country" should have these available values set:

10. The City parameter will have the following available values based on the CityLookup data set: