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, '',''))
3.138.117.75