Now, let's set up a cascading parameter between Category and Subcategory. We want to only show subcategories that belong to a particular category. So, if we select Bikes on our Category parameter, we want to see only the subcategories that belong to bikes, that is, mountain bikes, road bikes, and touring bikes.
Let's create a stored procedure named dbo.uspSubcategory. Run the script called uspSubcategory_Ch01.sql included in this book.
After we create the dbo.uspSubcategory stored procedure, let's create a new dataset out of that procedure. Let's name it ds_Subcategory:
The uspSubcategory stored procedure accepts a Category variable. So, under the hood, what we're trying to set up is to pass the value of the category field to the parameter of the Subcategory.
By default, the report designer detects all the parameters in the dataset (in this case, a stored procedure). The Parameter tab of the Dataset Properties dialog shows all the parameters detected by the report designer.
After setting up the properties for our new dataset, let's set up the @Subcategory parameter to use the same dataset:
We should now see that our Category parameter cascades to the Subcategory parameter:
Note
Please make note that we only needed to pass a @Category parameter to our Subcategory stored procedure without doing any other setup or process on the Report Designer/Manager side, because, both the @Category and @Subcategory Report Parameters are on the same scope, which is this report (SalesdetailsReport.rdl).