How use NULL value SSRS parameter to retrieve rows containing NULLS

Question

I've read many posts concerning NULLS and SSRS report parameters, but none of them appear to solve my problem.

I have a user-selectable SSRS report parameter @NoteType which can be 1, 3, 4 or NULL. The database table I'm working with (eventText) has a column NoteValue containing 1 of those 4 values. Unfortunately, I can't alter the table design to substitute an integer for that NULL value.

My report works fine if @NoteType = 1, 3 or 4; but not if @NoteType = NULL. If @NoteType = NULL then, depending on my paramaterized WHERE clause (discussed below) my report has either no rows, or it has all of the rows for which NoteValue = 1, 3 or 4. I can't figure out how to retrieve the rows for which NoteValue = NULL if the user selects @NoteType = NULL.

I've configured the @NoteType parameter to allow NULL values, with Note Value as the parameter's value field and Note Type as the label field. The parameter values are obtained from a UNION query:

SELECT      ett.NoteValue         AS    [Note Value],
            ett.NoteDescription   AS    [Note Type]
FROM        eventTextType    ett
UNION       
SELECT      NULL                  AS    [Note Value],
            'Blank'               AS    [Note Type]
ORDER BY    [Note Type]

The eventTextType table has only 3 rows in which the NoteValue column value is 1, 3 or 4 respectively. There is no row with NoteValue = NULL in the eventTextType table, but there are many such rows in the eventText table. That's why I'm using a UNION query to include NULL as one of the @NoteType parameter values.

The UNION query's output is:

Note Value  Note Type
4           Annuity 
1           Balance
NULL        Blank
3           Division

I suspect that my problem is in the WHERE clause which uses the @NoteType parameter to retrieve rows for the report. Here's what I have tried:

WHERE et.NoteValue = @NoteType -- works fine for @NoteType = 1, 3 or 4; but no rows are returned if @NoteType = NULL

WHERE et.NoteValue = IIF(@NoteType IS NULL, NULL, @NoteType) -- same result

WHERE et.NoteValue = CASE WHEN @NoteType IS NULL THEN NULL ELSE @NoteType END -- same result (expected, since this is equivalent to the IFF version)

WHERE et.NoteValue = COALESCE(@NoteType, et.NoteValue) -- works fine for @NoteType = 1, 3 or 4; but if @NoteType = NULL returns all rows with NoteValue column value 1, 3 or 4 but no rows with NoteValue column value = NULL

What WHERE clause should I be using?

Answer 1

Assuming that your dataset for your main dataset is a dataset query (not a stored proc) then I would simply add a valid parameter value (I've used 0 in this exmaple) to the parameter list and substitute that in the dataset query.

So, change the dataset query that provide parameter values to

SELECT      ett.NoteValue         AS    [Note Value],
            ett.NoteDescription   AS    [Note Type]
FROM        eventTextType    ett
UNION       
SELECT      0                     AS    [Note Value],
            'Blank'               AS    [Note Type]
ORDER BY    [Note Type]

Now in you main dataset query you can change your where clause to this (which will also handle multi-value parameters if that's required)

WHERE ISNULL(et.NoteValue, 0) IN(@NoteType)

Unless I've missed something obvious, that should be it.