Wednesday, March 14, 2012

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.

12 comments:

Ronald Voets said...

Thanks Andre. I've tried it and it works great

Debbie Leander said...

Nice and simple and worked like a charm. Thanks!

Mark Schuh said...

That worked beautifully! Thank you for the information.

SAMA said...

You the star buddy...Thanks for the post saved my day

Jennifer Hart said...

It was nice to find the exact syntax and instructions in a post. Often, I'll find a great example but one step is confusing, rendering the article useless. Great job!

Jennifer Hart said...

It was nice to find the exact syntax and instructions in a post. Often, I'll find a great example but one step is confusing, rendering the article useless. Great job!

Cj said...

Where are you placing the query that's under Change this query to?

Cj said...
This comment has been removed by the author.
André van de Graaf said...

CJ, you need to place the query in the dataset of your report.

bhargav kandala said...

Thanks for the excellent post, but i'm using ORACLE DB as the source and when i used NVL or COALESCE i'm getting errors as invalid identifier.Any ideas?

bhargav kandala said...

Thanks for the excellent post, but i'm using ORACLE DB as the source and when i used NVL or COALESCE i'm getting errors as invalid identifier.Any ideas?

lee woo said...

Women should never go without earrings. Passing on them is an opportunity missed. See the link below for more info.

#passing
www.ufgop.org