We may have noticed that the cascading functionality between the Category and Subcategory works only when we have a single category. If we select multiple Categories, we want to get all the Subcategories (multivalued) under those Categories.

We might think that setting up the @Subcategory parameter to "allow multiple values" would do the trick. But that won't work:

I've seen people handle this requirement by using custom codes that are processed on the report server.

It's a good practice that anything that can be processed on the SQL Server database should be processed on the database.

With the use of the table-valued function (MultiValueParamSplit) we created previously, we can set the @Subcategory parameter to accept multiple values without modifying our report. This is the primary reason why we should prefer using stored procedures for our datasets.

Let's modify the dataset behind the Subcategory parameter by modifying the dbo.uspSubcategory stored procedure.

We only need to change the WHERE clause from:

Select…
From…
WHERE Category = @Category

A clause that uses our MultiValueParameterSplit function:

Select…
From…
WHERE Category IN (SELECT [Category] FROM dbo.MultiValueParamSplit(@Category, ',')

We should now see a cascading effect between the multivalued Category and Subcategory parameters:

To make use of these cascading subcategories, we still need to modify the underlying dataset of our report which is the tblSalesReport to accept the multivalued subcategory parameter. To do that, let's modify the stored procedure ds_SalesDetailsReport.

Let's change the WHERE clause of the @Subcategory parameter from:

Select…
From…
Where Subcategory = @Subcategory
to
Select…
From…
Where Subcategory IN (Select [Category] FROM [dbo].[MultiValueParamSplit](@Subcategory, '',''))