Cascading Parameter in SSRS

ByHariharan Rajendran

Cascading Parameter in SSRS

Cascading parameter means, one parameter values are based on another parameter value.

This article explains this concept in a very simple way with the below scenario.

Pre-Requisites:

  1. Main Dataset with 2 parameters
  2. Dataset for parameter 1 distinct values
  3. Dataset for parameter 2 distinct value with parameter 1 in where clause

Main Dataset:

Select EnglishProductCategoryName as ProductCategoryName, EnglishProductSubcategoryName as ProductSubCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Where EnglishProductCategoryName=@Category and EnglishProductSubcategoryName=@SubCategory

Dataset 2 for Parameter 1 Values:

Select distinct EnglishProductCategoryName as ProductCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Dataset 3 for Parameter 2 values with Parameter 1 in where clause:

Select distinct EnglishProductSubcategoryName as ProductSubCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Where EnglishProductCategoryName=@Category

Assign the available values for both parameters

Run the report.

Initially, it will show the first parameter with drop down values and the second parameter will not show any values.

As per the above scenario, we are passing the first parameter value to the second parameter.

We need to choose a value of the first parameter so that we can see the second parameter values.

Based on the first parameter value, second parameter values are changing.

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Leave a Reply