ssrs:add null value to multi value parameter didn't work

Question

in order to allow a selection of null value in multi value parameter in SSRS project we can use the query for the data set

Select ID,Value From SpecificTable
Union All
Select NULL,'Other' 

in order to let null value returned from parameter when 'other' string is selected from parameter available selections

note:in available values section at parameters properties i have chosen ID Column as Value Field and Value as Label Filed

i have tried the previous query with no be benefit ,the string 'other' doesn't appear as available selection for the parameter when i preview the report

are there any additional configurations that i should apply ?

Answer 1

I don't think you can do it this way... that's why "Allow Nulls" is an option.. what you can do is set the ID value for Other to some obscure value.. like -99.. which the actual ID from the specifiectable can never be.. and modify your stored procedure to say..

where (@id = sometable.someidcolumn or @id = -99) 

This should in effect ignore the ID selection! Unless you want it to do something else of course.. then you code accordingly in the where clause.