SSRS: Multi-Value Parameters and NULL values
Wednesday, June 22, 2011
In SSRS, multi-value parameters cannot contain null values. If you create a multi-value parameter in SSRS and check the option “Allow null value”, the following error message is displayed.
I recently encountered a scenario where I wanted to include a ‘NULL’ option in a multi-value parameter. I implemented a solution using the SQL UNION operator and the ISNULL function. Details are included below:
I first created a dataset to populate the multi-value parameter. The query to populate the dataset uses the UNION operator so an additional value can be added to the output. This value is used to represent the ‘NULL’ option. I decided to use the string ‘(NULL)’ but you can use any desired value that matches the ‘ColumnName’ type (String in this example).
Select Distinct(ColumnName) From TableName Where ColumnName is not NULL UNION Select '(NULL)' Order by ColumnName
- In the report parameter properties ‘General’ tab, I selected ‘Allow multiple values’. On the ‘Available Values’ tab I selected the dataset the I created above.
- I also created a dataset that returns the data to populate a table in my report. I used the ISNULL function in the Where clause to map the NULL values to the string ‘(NULL)’ so it matches to the string in the parameter.
Where ISNULL(<DatabaseColumnName>,’(NULL)’) IN @ParamName
- Now, if the value in the database is NULL it is mapped to the string ‘(NULL)’ which matches the string in the parameter.