SSRS Cascading Parameters

In SQL Server Reporting Services or SSRS, Cascading parameters means one parameter value will be filtered depending upon other parameters. In this article, we will show you how to configure SSRS Cascading parameters example.

We are going to use the below-shown report to explain, SSRS Cascading parameters in Reports. Please refer to the Table Report article to understand the SSRS table report.

If you observe the below screenshot, It was a standard Table report with Country, State, City, Full Name, Occupation, Yearly Income, and Sales Amount columns.

The below screenshot will show you the Data Source and Dataset we used for this report.

SQL Command we used in the above screenshot is:

SELECT Geo.[EnglishCountryRegionName] AS [Country]
      ,Geo.[StateProvinceName] AS [State]
      ,Geo.[City]
      ,Cust.FirstName +' '+ Cust.LastName AS [Full Name]  
      ,Cust.EnglishEducation AS Education 
      ,Cust.EnglishOccupation AS Occupation
      ,SUM(Cust.YearlyIncome) AS YearlyIncome
      ,SUM(Fact.SalesAmount)AS SalesAmount 
FROM  DimCustomer AS Cust
  INNER JOIN
      FactInternetSales AS Fact ON 
 Cust.CustomerKey = Fact.CustomerKey
 INNER JOIN
     [DimGeography] AS Geo ON
   Cust.GeographyKey = Geo.GeographyKey
 GROUP BY Geo.[EnglishCountryRegionName]
         ,Geo.[StateProvinceName] 
	 ,Geo.[City]
	 ,Cust.FirstName
         ,Cust.LastName  
         ,Cust.EnglishEducation 
         ,Cust.EnglishOccupation 
  ORDER BY [Country]

Configure SSRS Cascading Parameters

To explain the Cascading Parameters in SSRS, we will create three Report parameters such as Country, State, and City. In the final report, If you select the United States as a Country, then the state parameter has to display all the states that belong to the United States. Next, If you choose New York as a state, then the City parameter has to show the cities present in New York using SSRS Cascading Parameters.

First, We are creating one Embedded Dataset holding Distinct country names from [DimGeography] table in [AdventureWorksDW2014]

SQL Command we used in the above screenshot is:

SELECT [EnglishCountryRegionName] AS [Country]

FROM [DimGeography]

Right-Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.

Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties. Please provide a valid Name, Prompt text, and Data type for the SSRS cascading Report Parameters.

Allow Multiple values: If you want to allow the user to select more than one value, Please check mark this option. Please refer to the Multi Value parameter article to understand the uses of multi-values.

Allow Multiple Parameter Values option 0

Next, We have to select Available Values. If you can specify the values manually, you can choose the specify values option and enter the values manually.

In this SSRS Cascading Parameters example, We would like to use the CountryName Dataset we created earlier. So, select Get Values from a query option, and select the Dataset from the list. Next, We are assigning [Country] as Value Filed and Label Field.

Drop Down List Parameters Label and Value Fields 3

Click Ok to finish configuring the Country parameter.

Next, we are creating one more Embedded Dataset holding Distinct State names from [DimGeography] table in [AdventureWorksDW2014]. SQL Command we used in the below screenshot is:

SELECT DISTINCT 
         A1.[EnglishCountryRegionName]
        ,A2.[StateProvinceName]
FROM [DimGeography] AS A1
    ,[DimGeography] AS A2
WHERE A1.[EnglishCountryRegionName] = A2.[EnglishCountryRegionName]
ORDER BY [StateProvinceName]

Next, We have to create one more parameter to hold the State information. So, Right-click on the Parameters Folder present in the Report Data tab and to select Add parameters.. option from the Context Menu.

Please provide a valid Name, Prompt text, and Data type for the Report Parameter. If you want to allow the user to select more than one value, checkmark this Allow Multiple values option.

In this SSRS Cascading Parameters example, We would like to use the StateDS dataset we created earlier. So, select Get Values from a query option and select the Dataset from the list. Next, We are assigning [State Province Name] as Value Filed and Label Field.

Click Ok to finish configuring the State parameter.

Next, we are creating one more Embedded Dataset holding Distinct City names from [DimGeography] table in [AdventureWorksDW2014]. SQL Command we used in the below screenshot is:

SELECT DISTINCT 
         A1. [StateProvinceName]
        ,A2.City 
FROM [DimGeography] AS A1
    ,[DimGeography] AS A2
WHERE A1.StateProvinceName = A2.StateProvinceName
ORDER BY City

Next, We have to create one more parameter to hold the City information. So, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.

Please provide a valid Name, Prompt text, and Data type for the Report Parameter. Checkmark this Allow Multiple values option to allow the user to select more than one value.

In this SSRS Cascading Parameters example, We would like to use the CityDS dataset we created earlier. Select Get Values from a query option and select the Dataset from the list. Next, We are assigning [City] column as Value Filed and Label Field.

We have successfully created three Datasets and three Parameters for Country Name, State Name, and City. Now, We have to apply filter conditions on the dataset. Please refer to Filter at Dataset Level article to understand the creation of Filters.

SSRS Cascading Parameters Example

To show the SSRS Cascading Parameters functionality, we have to apply the filter conditions. So, select the Dataset from the Report data tab, and Right-click on it will open the context menu. Please select the Dataset Properties.. option from it

Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties.

First, select the previously created StateDs and add the below-shown filter. This filter allows the Report to filter the states (parameter data) as per the selected Country.

Second, Select the previously created CityDs and add the below-shown filter. This filter allows the Report to filter the Cities (parameter data) as per the selected state.

In this SSRS Cascading Parameters example, we are going to display the records whose [Country Name] present in [Country Parameter], [State Name] present in [State Parameter], and [City Name] present in [City Parameter]. So, we are creating three filters for a country, state, and City.

Here we are using Operator as IN Operator. If you find any difficult to write the parameter name, Please click on the fx button and select the parameter name from the GUI.

Click Ok to finish configuring the Filters at Dataset Level. Let’s click on the Preview Tab to preview the data based on SSRS Cascading Parameters.

From the above screenshot, you can observe that It is displaying a Blank Report allowing us to select Multiple values from the Country names only. The remaining parameters for State and City values are disabled. Once you selected the Country Name (s) then State Parameter will be enabled for selection and also displays the states correspond to the selected countries. Its called Cascading Parameters in SSRS.

Here we are selecting the United Kingdom as Country Name so, it is displaying the states inside the United Kingdom (as per Dataset). Next, We chose England from the United Kingdom.

Here, we are selecting Birmingham, Liverpool, and London cities present in the United Kingdom

Let’s preview the data by pressing the Enter button in SSRS Cascading Parameters report