Reporting Services: Allow Blank Value for a multi-valued Parameter

I have a multi-value parameter "Keyword" that also allows blank value. When I try to view my report with a default null Keyword value, it doesn't display any. Please help.

Thanks!

Gerri

I assume you want to return everything if the parameter is null, right? does you query support null parameters? For instance, if my query says:

select * from table where field IN(@.parameter)

not sure of the technical way to explain this, but a null parameter would return no data because null is not a value in "field". In fact, I doubt that a null parameter would even result in a dataset where the value of "field" is null (never thought about it). I would guess, (disclaimer - I could be wrong here), that a null parameter is an object rather than a value, therefore it would not be the same as "is null" in sql server. I would guess again that it is more like IsNothing in RS, and therefore the database has no idea what to do with it. Maybe someone could verify that.

Anyway, you could try something like this:

select * from table where (IN(@.parameter) or (@.parameter is null))

that way, if your parameter is null, the where clause basically only TESTS if the parameter is null (which it is) and does nothing else.

|||

Thank you.

I would also like to ask. I have 7 parameters for my report. 3 of which are coming from UI. and 4 are visible in report viewer. When I set a value from the parameter in RS and click 'View Report', the value is set to null, thus, disregarding the values of these 4 and considering only those 3 coming from UI. Please help.

Thanks!

Gerri