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.

14 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!

Unknown said...

That worked beautifully! Thank you for the information.

SAMA said...

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

Unknown 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!

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

Unknown 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?

Unknown 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?

Erkan Aytar said...

Thanks,

Unknown said...

I'm getting an error "Invalid Column Name" for
"ISNULL(Col1, 'NULL') as coldesc"

Vinicius said...

Tnk so Much! Im from Brazil, you saved my day, for many times i search for this question. Finally i found here