Enumerating Parameter Collection in SQL Server Reporting Services

  • Article

Many of you often tried to and ask for how to enumerate the Parameters the collection. The MSDN has a very clear statement about that:

“You cannot use a Microsoft Visual Basic For Each construct to step through the collection. You need to know the name of a parameter defined in a report definition before you can reference it in your code.”

https://msdn.microsoft.com/en-us/library/ms178770.aspx

 So, at the end it is not possible at all without knowing the exact item/parameter name.

 The common scenarios I saw for that are to build a generic function which can be included easily in the report to get the parameter values, display them somewhere in the report of write them to an Audit procedure. Some implementations I did wanted to have the parameters selected by the user persisted to the database (configurable, based on a configuration table as being sensitive information in conjunction with the user information). So the main question is, how to get the information out of the parameters ?

Well, the following Function does all that, delivering the Parameters in the form of:

 

Single Value parameters: SomeParamName:TheValue

MultiValue parameters: SomeMultiParameter:TheValue1|TheValue2

Internal Parameters: (internal):SomeValue (They do not have a Label value)

 

The Function is also able to ommit internal parameter using a second signature:

Public Function GetParameterDefinitionAndValues(ByVal parameters as Parameters, ByVal ParameterString AS String, ByVal ShowInternalParams As Boolean) as String
Dim OutputString as String
Dim OutputStringTemp as String
Dim param as Parameter
Dim Label as String
Dim Internal As Boolean

OutputString = ""

'If no parameter is passed we can discard the input and exit the function
If ParameterString.Length = 0
Exit Function
End If

'Get the string in an enumerable format
Dim ParamStringArray() as String
Dim paramString as String
ParamStringArray = Split(ParameterString,",")

For Each paramString in ParamStringArray

OutputStringTemp = ""

Try
param = parameters(paramString)
If (NOT (param.Label Is Nothing) OR ShowInternalParams = true OR param.IsMultiValue)

'For some parameters the Labels can be empty (hidden / internal ones)
If param.IsMultiValue then
For i as integer = 0 to param.Count-1
OutputStringTemp = String.Format("{0}{1}({2})|",OutputStringTemp,param.Label(i),param.Value(i))
Next
Else
OutputStringTemp = String.Format("{0}{1}({2})|",OutputStringTemp,param.Label,param.Value)
End If

OutputStringTemp = LEFT(OutputStringTemp, LEN(OutputStringTemp) -1)

OutputString = String.Format("{0}{1}:{2};",OutputString,paramString, OutputStringTemp)

End If
Catch ex As Exception
OutputString = OutputString + paramString + ":" + ex.Message
End try

Next
Return OutputString
End Function

Public Function GetParameterDefinitionAndValues(ByVal parameters as Parameters, ByVal ParameterString AS String) as String

                Return GetParameterDefinitionAndValues(parameters,ParameterString ,false)

End Function

 

-Jens