다음을 통해 공유

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

Comments

  1. Do I assume correctly that the expression in the report should call the bottom Function "GetParameterDefinitionAndValues" (the one with 2 parameters)? 2.a. What is the meaning of the 2 parameters: "parameters" (type Parameters), and "ParameterString" (type String)? 2.b. Where should the respective parameter values come from? Concerning the former: I do not know how to reference the collection of parameters (to me, that is exactly the problem to solve). Concerning the latter: I do not know what this is for (see question 1). I would think it is not necessary to pass the string of parameter values, because those could be extracted in the inner function (with 3 parameters), using the (enumerated) parameter. FYI: I am using Microsoft SQL Server 2008 R2 and Report Builder 3.0.
  • Anonymous
    June 03, 2014
    Hi Semprini,
  1. You are correct. 2a. You would pass the ReportParameter object (as sample can be seen here: blogs.msdn.com/.../sql-server-reporting-services-logging-caching-project-released-on-codeplex.aspx) 2b. The problem is that there is no enumerator (as mentioned above) you will especially specify the name of the Report parameter name. If you do not know that by the time that you write the call for the function, you have no luck. -Jens