报表运行参数为空，Allow NULL value in Multi Value Report Parameter in SQL Server Reporting Services

Problem

Your SQL Server Reporting Services (SSRS) report has a multi value parameter, but it doesn't show NULL in the parameter drop down along with the other parameter values. In SSRS a multi-value parameter cannot include a NULL value, so users can't filter the data for NULL values. Your requirements state a need to be able to filter the data for NULL values, so in this tip I will demonstrate how to allow NULL values in a multi value SSRS report parameter.

Solution

This tip assumes that you have previous real time work experience building a simple SQL Server Reporting Services (SSRS) report with parameters.

I have prepared this tip in such a way that a beginner SSRS Developer can also understand the problem and implement the solution. In this tip we will first look at the problem with a simple example and later I will demonstrate the solution. I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Let's understand the problem with a simple example

I have already created a data source connection to the AdventureworksDW2008R2 sample database. Let's create the below two datasets for the sample report.

ReportDataset: This dataset will be used for the report body.

SELECT     ProductKey, EnglishProductName, Size
FROM       DimProduct
WHERE      Size IN (@ProductSize)
ORDER BY   ProductKey

SizeDataset: This dataset will be used to get a list of values for the ProductSize report parameter. This dataset will return one NULL value. You can verify it.

SELECT     Distinct Size
FROM       DimProduct
ORDER BY   Size

ReportDataset has one query parameter named @ProductSize, so SSRS will automatically create one report parameter name as ProductSize. After creating both datasets, your Report Data pane will look like the below image.

Let's configure the ProductSize report parameter. Right click on the ProductSize report parameter and click on the Parameter Properties, it will open the Report Parameter Properties window. Please make the below changes in the properties for the ProductSize report parameter.

Let's add a Tablix for data viewing purposes. Please pull all the data fields from ReportDataset into the Tablix. Your report will look like the below image after adding the Tablix.

Let's preview the report; As you can see from the below image, the ProductSize report parameter doesn't show NULL in the drop down list. So the report will not show products where the Size is NULL. You can verify this by previewing the report.

If you will try to enable "Allow null value" for the ProductSize report parameter, you will get this error message “A multi-value parameter cannot include null values” as shown below.

You get this error because if you perform any String or Mathematical operation with a NULL value then result will be NULL that is why NULL is not allowed in a multi value report parameter. How can we allow a NULL value in a multi value parameter? I will demonstrate the solution below.

Steps to allow NULL value in Multi Value Parameter in SSRS

SizeDataset is responsible to return the list of values for the ProductSize report parameter. We have to modify SizeDataset in such a way that NULL can be displayed in the Productsize report parameter dropdown list.

Modify the SizeDataset with the below query.

SELECT     Distinct ISNULL(Size,'NULL') as Size
FROM       DimProduct
ORDER BY   Size

I have used the ISNULL() function in the above query which will return NULL (a string value) if the NULL value is found for Size. Now SizeDataset will return NULL along with the other list of values in the ProductSize report parameter dropdown. You can refer to the below image.

We have to modify the ReportDataset in such a way that when NULL is passed from the ProductSize report parameter thenReportDataset must return the products for which Size is NULL.

Modify the ReportDataset with below query.

SELECT     ProductKey, EnglishProductName, Size
FROM       DimProduct
WHERE      ISNULL(Size,'NULL') IN (@ProductSize)
ORDER BY   ProductKey

I have used ISNULL() again in the where clause so the report parameter value can match the where clause value.

We have made all necessary changes, now let's preview the report. As you can see from the below image, our sample report is returning all the records with NULL values.