Displaying Multi Value Parameters in Reports

So you've got a multi-valued parameter in your report and as per best practice you want to display the selected values on the report, usually in the report header. But what if the list of parameter values is long, e.g. 10 or more values? If the user has ticked "Select All" in the parameter, you often don't want to show every individual value e.g.

MyParameter: Value1, Value2, Value3, Value4, ..... Value15

Usually if the user ticks "Select All" it's preferable to just display something like:

MyParameter: All

I've seen a few ways of accomplishing this, often involving clunky SQL in the dataset that provides the report values to create an artificial "ALL" option in the data. However, my preferred option is simpler, and I think a little more elegant (I don't claim to have invented this by the way, it's just the best way I have seen it done).

The textbox you are using to display the parameter values will probably already contain an expression like:

=Join(Parameters!MyParam.Value, ", ")

to concatenate the selected values in the multi-value parameter. Try replacing it with this:

=Iif(IsArray(Parameters!MyParam.Value),

Iif( CountRows("ParamDataset")=Parameters!MyParam.Count,

"All",

Join(Parameters!MyParam.Label, ", ") ),

Parameters!MyParam.Label)

where MyParam is the report parameter and ParamDataset is a dataset that provides the available values for the report parameter.

What's happening here is the code first checks to see if the value of the parameter is an array (if it is, then the parameter is a multi-value parameter). It then counts the number of rows in the dataset supplying the available values for the parameter (i.e. the total available values) and compares this to the number of values in the array. If these numbers are equal, this means that all available values have been selected (i.e. "Select All" in the parameter options) and so the textbox will just display the string "All". If they are not equal, then it just concatenates the selected parameter values (or labels) together into a string.

It's not perfect, because if the user selects all but one parameter values from a large list then they still get a long concatenated list, but I in my experience this works well for many clients.