SSRS: Pass Multi-Value Parameters to Stored Procedure


Using a multi-value parameters in SSRS reports is giving the users the flexibility to filter on one or more values. In the following example they can filter on one or more products and on one or more customers.

SSRS is passing the parameters to the stored procedure as joined strings. This means that it is very important to use a “safe” delimiter. Comma (,) or semicolumn (;) are not good enough, because they may exist in the filtered column (we may filter on column Part and a part ABC,DEF is an existing one… :-(). Of cource, the best practice it to filter on “Value-Label” pair where “Value” is the ID column of the structure table, but not always the data is clean enough.

I use CHAR(164) –> ¤ as a delimiter. It is not on the keyboard and most likely doesn’t exist as a data.

Datasets for the SSRS parameters.

SSRS Datasets

This example is just a preview of the logic that is using a dynamic SQL to convert the SSRS multi-value parameter to IN statement.
I will expand the code to check if the passed values are “ALL” and if “ALL”, the parameter will not be included in the WHERE clause.

Create database

Create stored procedure

Test stored procedure

Test stored procedure

Delete database

udf_SplitString() is a great resource when CHAR(164) –> ¤ is not a good delimiter.

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *