How to pass NULL value to a Multi value parameter in SSRS?-优快云博客

本文介绍如何在SSRS报表中配置一个可以接受包括NULL在内的多个值的参数。通过使用ISNULL函数将NULL值转换为空字符串，并调整数据集查询以支持这种变化。

How to pass NULL value to a Multi value parameter in SSRS?

This week I tried to build a SSRS report with a parameter which can have multiple values including the NULL value. This is not possible by default in SSRS. When you configure the parameter with option: Allow null value and Allow multiple values. You will receive next error message:

A multi-value parameter cannot include null values.

The reason for this is the TSql syntax of the underlying dataset.

SELECT Column1, Column3
FROM TableX
WHERE Column2 IN (@MyParameter)

This query will fail for the NULL value because IN syntax is in conflict with NULL value. TSql syntax for NULL values is

SELECT Column1, Column3
FROM TableX
WHERE Column2 IS NULL

Solution:
We are going to use the ISNULL function to change parameter values to a blank value (''). Assume you have a dataset to retrieve parameter values for @MyParameter.

SELECT DISTINCT(Col1) FROM TableB

Change this query to:

SELECT DISTINCT ISNULL(Col1, '') AS Col1, ISNULL(Col1, 'NULL') AS Col1Description
FROM TableB
Change the Report Parameter Properties to: Allow blank values ('') and Allow multiple values.
Change the available values: Value field: Col1 , Label field: Col1Description.

Now we need to change the query of the dataset.
-- Original query
SELECT Column1, Column3
FROM TableX
WHERE Column2 IN (@MyParameter)

-- Changed query
SELECT Column1, Column3
FROM TableX
WHERE ISNULL(Column2,'') IN (@MyParameter)

That's all, enjoy it to make your report more user friendly.