Welsh Corgi (1/24/2012)

So if I understand your requirements correctly, you want the user to be able to execute the report without making a selection in the State parameter drop-down list, and if the user does this, the report should return results without regard to the value in the State column (i.e., the report will not be filtered by State), yes?

If so, you could set the default value of the State parameter to '-ALL-' in SSRS so that the user can view the report without making a selection in the drop-down box, then rewrite the WHERE clause in the query like this:

WHERE '-ALL-' IN (select Item from dbo.DelimitedSplit8K(@State, ',')) OR State in (select Item from dbo.DelimitedSplit8K(@State, ','))

You should have discovered that a multi-value parameter cannot have a NULL value (at least, not in SSRS 2008 - I'm pretty sure it's the same in SSRS 2005). Adding a NULL value to the drop-down list for the SSRS parameter will not help with this technique because the condition

WHERE State in (NULL)

resolves to UNKNOWN (neither TRUE nor FALSE) , and because all conditions in the WHERE clause must resolve to TRUE for any row to be returned, you will never get any rows.

If that doesn't help, could you post the full stored procedure code and more details about your SSRS parameter (is the drop-down list populated by a query, what do you have for the default value, etc.)?