Parameter Tips and Tricks (Volume 1)

It seems clear to me that, from the number of posts on the topic, parameters are a not the easiest report items to work with for many of our customers. Until some of your suggestions make it into the product, I would like to make it a bit easier for you to use parameters. To that end, I thought I would share some of the tricks I have learned to make parameters a bit more useful.

Showing the Selected Values in your Report

In order to display the selected values of a parameter in your report, you need to know whether or not the parameter allows multiple values.

Single-value Parameter

If you are using single-value parameters, the solution is trivial: set the expression of the textbox in which you wish to display the value to

=Parameters!ParameterName.Value

You can use ‘=Parameters!ParameterName.Label’ to display the label instead.

Multi-value Parameter

If the parameter is a multi-value parameter, then the expression gets a bit more complicated. Multi-value parameters are represented as arrays and the expressions that use them need to account for that. Using the above expression on a multi-value parameter would result in an error at view time. Report Builder is a bit nicer when selecting a multi-value parameter from the values list in the expression builder; it builds an expression that will not result in an error, but it is not particularly useful either: ‘=Parameters!MyParameter.Value(0)’. This will only give you the first parameter value in the selection. The easiest way to change the expression to handle an arbitrary number of selected parameter values is to use the JOIN function, like this:

=Join(Parameters!MyParameter.Label, “, “)

The first argument to the JOIN function is an array; the array index (0) is not needed (nor expected) in this context.

Using the JOIN function works well unless the user has selected a very large number of parameters (or has chosen the ‘Select All’ option). This could result in a spectacularly long list which might not be that useful. In this case you might want to limit the number of items to show:

=iif(Parameters!MyParameter.Count<5, Join(Parameters!MyParameter.Label, “, “), “5 or more!”)

Select All: Special Case

Suppose that you want to show the entire list of selected parameter values, unless the user has selected all the values, in which case you want to display ‘All’. This requires a bit of a trick.

First: the chicken or the egg? There is no way to get the count of all the possible parameter values in a parameters list at view time unless the user selects them all, but that does not help because, without knowing what that total is, we cannot tell that the user has selected them all.

The trick: hide a parameter that tells us the total! Suppose you have a dataset that drives the available values of your parameter:

SELECT id, name FROM people

You can create a new dataset (CountValues) that counts the values that your existing parameter will use:

SELECT COUNT(id) AS ValCount FROM people

Now create a new parameter (MyCountParameter) with these properties:
Data Type: Integer
Allow Null: False
Allow Multiple Values: False
Parameter Visibility: Hidden
Available Values: None
Default Values: From Query “CountValues” (Value field: ValCount)

Finally, set this as the expression on an appropriate textbox in your report:

=iif(Parameters!MyParameter.Count<Parameters!MyCountParameter.Value,Join(Parameters!MyParameter.Label,","),"All")

More to come later.