Overview: A quick review of how to display Single-Value and Multi-Value parameter selections within an SSRS report heading.

Level: Basic familiarity with parameters is assumed.

Placing Parameter Values in a Report Subheading

Let’s first chat about what the goal is here. If you have a parameterized report in which the user makes a selection, it’s a best practice to include those parameter values in a subheading (if not elsewhere within the report itself). The objective is for the report to clearly state what is being displayed, especially if the report consumer is not the original author.

With the parameter displayed in the heading, if a user prints or saves the report for later, the parameter selections made at the time are preserved. This adds value to a report, and aids understandability for others – especially when you have a more complicated report with numerous parameters. For example:

Using the “Value” or “Label” Property

When using the Parameters! collection, you have 4 properties available: Value, Label, Count and IsMultiValue.

Value: The Value field from when the parameter was set up. This is the actual value being used to drive the logic for what the report parameter needs to do. If the report parameter is associated to a dataset parameter (the most common use of parameters), then you may parameterize the source query on something like Customer Key from a data warehouse. When using an SSAS data source, this might be a unique member such as: [Customer].[Customer].&[35]. The Value field may or may not be suitable to display to a user on the report.

Label: The Label field from when the parameter was set up. This is a user-friendly label shown to the user. This might be a Customer Name, for example (rather than the Customer Key used for the Value). Most often you want to correspond the Label to what’s being displayed in a report heading.

Count: The count of the number of values selected. This is only useful for parameters which have been set to “allow multiple values.”

IsMultiValue: A True/False indicator that multiple values are permitted. Useful for conditional logic.

Displaying a Single Parameter Value

The expression in the heading shown above is using the Parameters! collection:

(Note that either Value or Label could be used in the expression. I used Value just to keep things simple.)

This syntax only works when you are displaying a single parameter value. Usage of a single parameter value is controlled by two things:

1. Usage of an = within the source query.

2. Ensuring the “Allow multiple values” checkbox within the Parameter properties is left unchecked. This tells SSRS it’s a single-value parameter.

Displaying a single parameter value is pretty straightforward. Let’s see what happens when we change to a multi-value…

Displaying a Multi-Valued Parameter

Following is an example of what we’d like to see if a multiple values are allowed for a parameter:

The expression in the heading shown above is using the Parameters! collection. However, usage of the JOIN() function is required when the parameter allows multiple values.

(Note that either Value or Label could be used in the expression. I used Value just to keep things simple.)

This syntax only works when you are displaying a multi-valued parameter. This is controlled by two things:

1. Usage of an IN() within the source query (rather than the = as used with a single-valued parameter) within the source query.

2. Ensuring the “Allow multiple values” checkbox within the Parameter properties is checked.

#Error in Textbox if JOIN() Function Not Used for a Multi-Valued Parameter

If you have #1 and #2 above set correctly for a multi-valued parameter, but you don’t use the JOIN() function within the expression for the heading, you’ll receive a warning when the report is executed and a “#Error” is displayed by the textbox.

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox8.Paragraphs[0].TextRuns[0]’ contains an error: Operator '&' is not defined for string "Year Selected: " and type 'Object()'. C:\Users\melissac\Documents\Visual Studio Projects\Blog_SSRS_Project\Blog_SSRS_Project\ParameterValueDisplay_MultiValue.rdl 0 0

Adding the JOIN() function fixes the above #Error from being rendered:

="Year(s) Selected: " & JOIN(Parameters!Year.Value, ", ")

Dealing With a Large Number of Parameter Values

If the number of possible parameter values is short, like the Year example shown above, then you’re done. But what if you have a super long list of possible values and the user selected an awful lot of them? That could make for a very unwieldy list of values in your report subheading. Depending on your requirements, a possible workaround might be:

="Year(s) Selected: " & IIF(Parameters!Year.Count > 10, “Multiple Years”, JOIN(Parameters!Year.Value, ", "))

In this example, the 10 is an arbitrary number I picked. In this situation, a better solution is probably going to be getting the data value into the report rows or columns (rather than the heading). Of course, that depends upon your requirements & the aggregation level of the report.