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

By:   |   Updated: 2015-05-18   |   Comments (25)   |   Related: > Reporting Services Development


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2015-05-18

Comments For This Article




Thursday, August 31, 2023 - 7:04:24 AM - Linh Mai Back To Top (91518)
Thank you so much for sharing this!

Saturday, July 16, 2022 - 1:19:06 PM - Joe Back To Top (90271)
SQL is working. You could also just replace the reportdataset all Nulls with 'NULL' (string).

Tuesday, November 19, 2019 - 3:57:04 AM - Rick James Back To Top (83131)

Doesnt work in Date-fields :-(


Wednesday, September 4, 2019 - 9:09:11 AM - Peter H Back To Top (82238)

An alternative to this solution, is to use a default value of "Unknown or NULL" for missing values.  This convention eliminates the need for testing for nullability through ISNULL or coalesce since missing values will have either of these values "Unknown or NULL" as the column value.  Although, my preference is coalesce over ISNULL.  It is a design convention that allows creation of an optional column without allowing the insertion of the nulls into the table.

Nice article!


Thursday, July 4, 2019 - 4:47:56 AM - Chandini patnaik Back To Top (81674)

Hi Ghanesh,

what can be done in a case where there are multiple parameter text boxes in the ssrs report but the report should only give result for the parameter having some values and ignore the empty parameters


Wednesday, July 3, 2019 - 9:52:21 AM - Jessica Back To Top (81664)

But how this is applicable when selecting the NULL value avoid selecting other value, because it allows multiple values, in other word the null value is the dominant in my case. how to apply this? If anyone can help.


Friday, June 7, 2019 - 10:44:01 AM - Eric Pare Back To Top (81368)

Exaclty what I needed!

Thanks!


Monday, April 29, 2019 - 6:16:57 PM - CORDELL Back To Top (79844)

 Worked like a charm.  Thank you.


Thursday, November 1, 2018 - 8:55:36 AM - Muhammad Ashikuzzaman Back To Top (78127)

Thanks, Nice & Essential info.

 

 


Friday, October 19, 2018 - 1:01:02 PM - peb Back To Top (77984)

 Hi,

does anyone know how to do this with fetchXML (crm online)...

or does anyone know if this isnt possible?

thx peb

 


Monday, July 2, 2018 - 1:15:24 PM - Tyler Back To Top (76501)

Thank you, thank you, thank you! Nice solution.


Wednesday, April 25, 2018 - 6:21:52 AM - [email protected] Back To Top (75779)

 

 I have created an ssrs report with 3 parameters.All the three are multi valued parameters.The default value for the parameters is "all".I want the report to run when "all" is unchecked i.e, the values are deselected.How to do this? 


Tuesday, March 13, 2018 - 10:45:09 PM - Mauli Back To Top (75405)

 Hello Ganesh

I have an issue with blank parameter label. One of drop down parameter values is 'xyz' and label is blank. It works fine on local machine. Multi-select parameter allows to select only one value - blank label. I can successfully execute report. However, SSRS server doesnt allow parameter with blank label, value is populated for this parameter option. I have set parameter to allow balnk value. However, I believe it shouldnt matter.  Any sugegstions? 

Thank you

Mauli


Wednesday, July 26, 2017 - 11:52:32 AM - Julie Kalu Back To Top (63287)

 

Just what I needed!  Thank you.


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

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 7, 2016 - 5:51:55 PM - Bob Back To Top (41828)

Excellent workaround!   Thanks.


Friday, April 1, 2016 - 6:13:43 AM - Param Back To Top (41115)

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 (39121)

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

Jon


Thursday, July 2, 2015 - 11:16:05 AM - Ghanesh Back To Top (38115)

Thank you all for your comments.

Keep learning!

Regards,

Ghanesh


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

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 (37224)

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 (37222)

 Thank You all

@Craig P A goof Idea, Appriciate it.

 


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

Good one..


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

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 (37208)

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















get free sql tips
agree to terms