Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Allow NULL value in Multi Value Report Parameter in SQL Server Reporting Services


By:   |   Read Comments (11)   |   Related Tips: > Reporting Services Filtering

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 then ReportDataset 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
Next Steps


Last Update:






About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, September 29, 2016 - 2:36:14 PM - Michael Back To Top

So I was able to replicate this just fine and it is functional, but for some reason my NULL parameter doesn't show NULL, it simple shows a box with no value next to it (this is in SSRS 2016). Is there something I can do so that I do show a NULL option to the user?

 


Thursday, July 07, 2016 - 5:51:55 PM - Bob Back To Top

Excellent workaround!   Thanks.


Friday, April 01, 2016 - 6:13:43 AM - Param Back To Top

IN my scenario datasource is sharepoint list and i want to apply filtters on the same.

But my list contain null or balnk value how can i avoid in parameter when i am using cascading paramter.


Monday, November 23, 2015 - 5:29:53 AM - Jon Ishaque Back To Top

Great tip! Really helped. And now have a clearer understanding of SQL/SSRS

Jon


Thursday, July 02, 2015 - 11:16:05 AM - Ghanesh Back To Top

Thank you all for your comments.

Keep learning!

Regards,

Ghanesh


Thursday, May 21, 2015 - 6:41:36 AM - King George Back To Top

Hi Ghanesh

Your series of articles on SSRS is very informative. Your style of explaining the scenario with pictures is excellent. Keep posting more articles.


Tuesday, May 19, 2015 - 9:52:14 AM - PhyData DBA Back To Top

Your method only works if the Mutli Value Parameter is a string data type. 

Casting NULL values to a Known value is not showing NULLS. You have just given a definition of a NULL Value in a dataset.
A ways to do this that works for all data types used to be in the Books online. 
This method shows the Actual NULL values without modifying the data values.

First step is to add a "OR @<parameter name> IS NULL"  in your dataset query where Clause. 

You will need to add some code to your select that is triggered by the selection of a True False report Parameter called "Show Null Values" that is selected to TRUE by default. 

This is not very detailed but most should get the idea.

It works for any Value Type, not just strings vlaues.

 


Tuesday, May 19, 2015 - 8:14:28 AM - Ghanesh Back To Top

 Thank You all

@Craig P A goof Idea, Appriciate it.

 


Tuesday, May 19, 2015 - 3:31:01 AM - prasad emani Back To Top

Good one..


Monday, May 18, 2015 - 3:44:02 PM - Craig P Back To Top

Sir, THANK YOU for making this clear.   I had done this, bu my method was ... messier.

My suggestion, appreciated by my users, was to replace nulls with not just the word "NULL", but "<null>".   the bracketing puts "<null>" at the top of the list in most sorts.

Thanks again!


Monday, May 18, 2015 - 11:11:36 AM - Kris Maly Back To Top

I enjoyed reading this Tip and followed the steps and created the report. Everything is crystal clear.

Recommend others to read.

This scenario is very obvious in the day to day job of a developer.

Thanks for educating the community and appreciate your volunteership.

Please keep publishing more and more articles.

I suggest that if you could make few articles on 'How to handle error in SSRS'. For this you deliberately make some mistakes and explain how to overcome. For example you showed one Error dialog box in this Tip.

Also give a thought that common errors encopunterd by developers. Hope youur experience will help others.

 

Thanks a lot


Learn more about SQL Server tools