How to set multiple values as Default Values for a multi-valued parameter (SSRS)

In most of the cases when we deal with parameters in SSRS reports, we provide some default value to those parameter (which can be a textbox, a drop down list etc) so that when users browse the report first time, report shows results with that default value of the parameter. And after that user can choose different values from the parameter list or enter different value in case of textbox to get the desired results.

 

This feature of having default value(s) can also be implemented in case of multi-valued parameter list. As in case of multi-valued parameter, user can select multiple values from the drop down list by selecting multiple checkboxes. So if we need to provide some default selection of values from this drop down list, we can provide a list of these default values for this parameter. This can be done using a Dataset for default values & then selecting this dataset as the query input for the default value.

 

Let’s suppose we have a multi-valued parameter named “Color” for displaying products based upon the selected list of colors.

Dataset for this report parameter will give complete list of colors.

 

SELECT DISTINCT Color FROM DimProduct

 

Now we’ll create another dataset only for the default values i.e. to select some of the colors by default to show the initial results on the report. This can be done by selecting some of the desired values based upon some condition or we can just have the predefined hard coded values.

 

SELECT ‘Blue’ As D_Color

UNION All

SELECT ‘Black’ As D_Color

 

And in the parameter “Color”, we can map these as: