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 SSRS 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.

SSRS Cascading Parameters 1

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

SSRS Cascading Parameters 2

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]

SSRS Cascading Parameters 5

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.

SSRS Cascading Parameters 6

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 Report Parameter.

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 SSRS Multi Value parameter article to understand the uses of multi-values.

SSRS Cascading Parameters 7

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 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.

SSRS Cascading Parameters 8

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]
SSRS Cascading Parameters 9

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.

SSRS Cascading Parameters 10

In this 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.

SSRS Cascading Parameters 11

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
SSRS Cascading Parameters 12

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.

SSRS Cascading Parameters 13

In this 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.

SSRS Cascading Parameters 14

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 in SSRS 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

SSRS Cascading Parameters 15

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 SSRS Report to filter the states (parameter data) as per the selected Country.

SSRS Cascading Parameters 16

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

SSRS Cascading Parameters 17

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.

SSRS Cascading Parameters 18

Click Ok to finish configuring the Filters at Dataset Level. Let’s click on the Preview Tab to preview the Data.

SSRS Cascading Parameters 19

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.

SSRS Cascading Parameters 20

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

SSRS Cascading Parameters 21

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

SSRS Cascading Parameters 22

Comments are closed.