Enumerating Parameter Collection in SQL Server Reporting Services
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