SSRS Multi-Value Blank

I have a SSRS report with a few dropdowns that are chained together. Basically a item is selected from dropdown 1 which then populates dropdown 2 and so on. The issue I am having is that in dropdown 3, which is a multi-value populated from a query I want to allow no selection.

Problem #1

Although that report parameter is set to allow blanks, it will not let me deselect all values. I get a required error.

Problem #2

In my stored procedure I have the parameter in question being filtered by an IN statement, which will not 开发者_如何学JAVAwork if a blank is coming though as I want the blank to skip that filter.

Problem #3

The dropdown #3 may not have any values once the other 2 are selected.

I feel that I am using the multi-value incorrectly causing all 3 issues. Any help is appreciated.

---

Problem #1

Can you add something like this to the end of your query that populates the DropDown?

Union
Select Null as (value field name), 'None' as (label field name)

Problem #2

Do you have to pass in an empty value? Can you do something like this:

WHERE (@Param is null OR t.id in @Param)

or even

WHERE (@Param ='' OR t.id in @Param)

If you want to keep it as a string?

Problem #3

Should be taken care of if you can do my suggestions for 1 and 2.

---

Courtesy of Naz at ANSA on the Microsoft Forums...

I have managed to create a much simpler solution, after many attempts. @Action parameter dataset is now:

SELECT ACTIONID, ACTIONDESCRIPTION, DATAAREAID FROM MXMSERVACTION WHERE (DATAAREAID IN (@CompanyID)) UNION SELECT '' AS ACTIONID, '(Blank)' AS ACTIONDESCRIPTION, @CompanyID AS DATAAREAID ORDER BY ACTIONID

This returned a ‘Blank’ value as well as all available action codes, in the parameter dropdown when running the report. I then referenced the parameter dataset in the main dataset as normal:

WHERE (MXMSERVCALL.ACTIONID IN (@Action))

Job done!