Blank Multiple Valued Parameter in SSRS

Question

I have several multiple valued parameters in my report. I don't want the user to select these parameters each time. These additional parameters need to be optional because there are other parameters needed in the report. Is there a way to make the Multiple Valued Parameters optional or Blank and still return data when any other parameters are selected?

Parameter names: Select Month, Select Year, Month over Month, Week over Week, User, User by BL Business Line

Thanks in advance for your help.

JC Ford JC Ford · Accepted Answer · 2015-03-11T05:14:50

You have to have something selected in the multivalue parameter, so just add a bogus value to the list with a label of "Any" or "All" or just an empty string; whichever makes more sense to your users. The bogus value should just be a value that isn't used by another option in the multivalue parameter. Then set that bogus value as the default value for the parameter. In your query, test for that bogus value like so:

SELECT MyField1, MyField2
FROM MyTable
WHERE (MyField in (@ParamValues) or 'BogusValue' in (@ParamValues))

With the above, if the "Any" item is selected the parameter is effectively ignored even if other values are selected.

Blank Multiple Valued Parameter in SSRS

1 Answers