Saturday, January 10, 2009

Advanced Reporting Services: Displaying Parameter Values

In Reporting Services, you can display the values of the Parameters collection by writing code like =Parameters!myParam.Value (or equivalently, =Parameters("myParam").Value). Displaying parameter values on the report itself is a good practice--it gives the end user some context in cases where the report has been printed out or exported.

Things get tricky when your parameters are multi-valued or contain labels. If Parameters!myParam.IsMultiValue is true, =Parameters!myParam.Value will render as "#Error". Also, If your parameter has a Label, you'll probably want to display that instead of the Value.

Instead of having to remember to change a report's parameter textbox every time I modify a parameter, I just reference the following code (copy-paste into Report > Report Properties > Code):

Public Shared Function GetParamValues(ByVal Param As Parameter) As String
 If Param.IsMultiValue Then
  If IsArray(Param.Label) Then
   GetParamValues = Join(Param.Label, ", ")
  Else
   GetParamValues = Join(Param.Value, ", ")
  End If
 Else
  If Not IsNothing(Param.Label) Then
   GetParamValues = Param.Label
  Else
   GetParamValues = Param.Value
  End If
 End If
 GetParamValues = IIF(Len(GetParamValues) > 100, Left(GetParamValues, 100) & "...", GetParamValues)
End Function

The parameter's textbox expression looks like this:

="Last Name: " & Code.GetParamValues(Parameters!last_nm)

Unfortunately, there's no way to get at a parameter's Prompt attribute (e.g., "Last Name") -- the ParameterImpl class only contains int Count, bool IsMultiValue, object Label, and object Value.