SSRS: Multi-value Parameter – (all), (blank), Value


Working with multi-value parameters in SSRS is always fun. We need to pick a good delimiter, add additional values like “ALL” or “BLANKS” and many others. In the following few lines I explain a logics to add a multi-value parameter to SSRS with additional values (all) to not apply the parameter and (blank) to select the blanks and NULLs.

The parameter’s default is (all) to facilitate the subscriptions. When (all) is the only selected option it is treated as (all). When (all) is selected together with other values, (all) it is ignored. The logic is that the default is (all) and the user doesn’t uncheck it while selecting the desired values.

Pick replacement characters that doesn’t exist on the keyboard and most likely in the data.
Delimiter –> CHAR(164) (¤)
Replacement for (all) –> CHAR(160) ( )
Replacement for (blank) –> CHAR(172) (¬)

Create test DB

Create test table

TestTable

Create ‘Split’ function to split the multi-value strings to array (table)

Create SSRS datasets for the parameters

SSRS_Par_FirstName

SSRS_Par_LastName

Create a dataset for the report and pass the parameters with Join(Parameters!Par_FirstName.Value, Chr(164))

Report

Delete test DB

Keep it simple :-)

 

Leave a comment

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