SSRS Multi Select Parameter, both Null and not null

Question

I'm new to SSRS, and have been tasked with a report that allows users to select NULL, non NULL or both.

For example

Name | ID
BOB     1
ALICE   2
DAVE    NULL
ROGER   NULL

Users need to be able to select Records with an ID value and/or records with no ID value. So it would be a multi select parameter (ID, NO ID , ALL).

I can make this work with a sql query, but am not sure how to make it work with user parameters that can be either, or or both.

The id field is a decimal.

Any advice for how to do this?

Thanks.

Answer 1

One way would be to include conditions like the following in the WHERE clause:

and (ID is null and 'NO ID' in @Parameter or
     ID is not null and 'ID' in @Parameter)

Alternatively, if the non-null ID values are always positive, you could use "NO ID" and "ID" as the parameter value labels, with the actual available values as 0 and 1 respectively, and use a condition like:

and sign(coalesce(ID, 0)) in @Parameter

(These conditions won't work in conventional SQL - where @Parameter will always be single-valued - but should work in SSRS.)

Answer 2

In the general tab of your Report parameter. There is an option for Allow Null Values. Check that box.

Also update your query to

From UserTable
Where (ID IS NULL OR ID IN (@IDparam))

Here are links if you need to know more abut Multi Select parameters http://jsimonbi.wordpress.com/2011/01/15/using-multi-select-with-stored-procedures/ http://technet.microsoft.com/en-us/library/aa337396(v=sql.105).aspx