CODEWITHSUNDEEP
checkboxreporting-services
Kim Jones
Kim Jones

Reputation: 133

SSRS 2005 parameters not returning any results when null checked

newbie to SSRS, running 2005. I hope someone can help!

I have a report with a parameter on it, ItemNum. I have it as a string, to allow NULL value and allow blank value, nothing in Available values, and null selected for Default Values. I then have in there WHERE part of my SELECT statement where item_num IN (@ItemNum).

when I preview the report, I get NO results if I:

leave the NULL checkbox checked (and obviously the Item # parameter empty, it's greyed out) or uncheck the NULL checkbox but don't put a value in the parameter.

the ONLY way I can get ANY results back is to enter a valid item number.

I would think that if you Allow Null values and/or Allow Blank values, it should just disregard that parameter. Perhaps it's because of it being in the where clause?

how can I make this function the way I need it to, i.e. if the NULL checkbox is checked (or unchecked and NO value entered) disregard that parameter (or in other words, return all records)?

also -- as a 2nd question -- is it possible to use the LIKE comparison with a parameter or does it always have to be IN?

Upvotes: 0

Views: 966

Answers (1)

Sir Crispalot
Sir Crispalot

Reputation: 4854

Try changing your WHERE clause to:

WHERE @ItemNum is null OR item_num IN (@ItemNum)

You need to explicitly check for NULL in this case.

In fact, why are you using IN for a single value? It's usually used to check for a number of values, e.g. WHERE item_num IN (1, 2, 3, 5, 10). A simple = would suffice.

Upvotes: 0

Related Questions