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

小鱼儿 2022-06-03 23:51 77阅读 0赞

##### 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.

![Report Data Pane Window][]

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.

![Report Parameter Properties][]

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.

![Adding 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.

![Multi Value Parameter Without NULL Value][]

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.

![Allow NULL In Multi Value Parameter Error][]

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.

![Multi Value Parameter With NULL Value][]

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.

![Report Preview][]

[Report Data Pane Window]: https://www.mssqltips.com/tipimages2/3502_Report_Data_Pane_Window.JPG
[Report Parameter Properties]: https://www.mssqltips.com/tipimages2/3502_ProductSize_Report_Parameter_Properties.JPG
[Adding Tablix]: https://www.mssqltips.com/tipimages2/3502_Adding_Tablix.JPG
[Multi Value Parameter Without NULL Value]: https://www.mssqltips.com/tipimages2/3502_ProductSize_Report_Parameter_List_Of_Values_Without_NULL.JPG
[Allow NULL In Multi Value Parameter Error]: https://www.mssqltips.com/tipimages2/3502_ProductSize_Report_Parameter_Allow_NULL_Value_Error.JPG
[Multi Value Parameter With NULL Value]: https://www.mssqltips.com/tipimages2/3502_ProductSize_Report_Parameter_List_Of_Values_With_NULL.JPG
[Report Preview]: https://www.mssqltips.com/tipimages2/3502_Report_Preview.JPG