SSRS 2008: Cascading Parameterized Report

In this article, we will see how to create a SSRS 2008 report with cascading parameters. We will create cascading parameters and add those to our report. For this demonstration, we will use SQL Server 2008 database ‘AdventureWorks’ for designing the report as well as parameters.

Note: In this article, I will be using the same demo which I have demonstrated in my previous article Designing Tablix Report in SSRS 2008.

Open SQL Server Reporting Services project ‘AdventureWorksTablixReport’. And go to ‘Preview’ tab to view the report –

Now go to ‘Report Data’ window and right click the ‘YearlySalesDS’ dataset and click on ‘Dataset Properties’. This will show you a Dataset Properties window as shown below –

Now replace the existing query with the below query –



Now if you observe the above query, we have added two parameters as shown below –
1) CategoryName
2) SubCategoryName

Preview the report and add ‘Bikes’ in Category Name Textbox and ‘Road Bikes’ in Sub Category Name and click on ‘View Report’ button. Your report will be similar to the following –


But that’s not what we are looking for. We want all the available Categories listed in a dropdown box and when we choose any of the category, the related sub categories should get loaded into the sub category dropdown box.

So let’s add a new dataset which will select all the distinct categories from the ProductCategory table. To add a new dataset, go to ‘Report Data’ window. Right click the data source and click ‘Add Dataset..’ as shown below –



This will bring up the ‘Dataset Properties’ window. Name the dataset as ‘CategoriesDS’. Click on the ‘New’ button in front of the ‘Data Source’ dropdown list and choose ‘Use Shared Data Source Reference’. From the dropdown list, choose ‘AdventureWorksDS’ data source and click ‘OK’ button.

Now paste the following query in the dataset properties box –

SELECT DISTINCT Name AS Category FROM Production.ProductCategory

Now your dataset properties window will look like the one shown below –



Click the ‘OK’ button. Now repeat the above steps (which we implemented for creating a dataset ‘CategoriesDS’) for creating ‘SubCategoriesDS’ with the following query –

SELECT DISTINCT PSC.Name AS Subcategory FROM Production.ProductSubcategory AS PSC INNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID WHERE PC.Name = @CategoryName

Now your Report Data window is having three datasets as shown below –



In the ‘Report Data’ window, expand the ‘Parameters’ section. You will see two parameters. Right click the ‘CategoryName’ parameter and go to ‘Parameter Properties’. From the left section make a choice of ‘Available Values’. Then in the right hand section, choose ‘Get Values from a Query’ option. This will show you the dataset dropdown box. Choose the ‘CategoriesDS’ dataset. Now choose ‘Category’ in the value and label field as shown below and click the ‘OK’ button –



Repeat the same steps for getting the value from a query for a SubCagetoryName parameter. Now preview your report –



If you observe, when you select ‘Categories’, the subcategories get selected/changed accordingly. This is how you implement Cascading effects on the Parameters in SQL Server Reporting Services.

Download the source code