I recently posted a snippet showing how you can enumerate the parameters for an SSRS server report. Here’s another approach that does the same thing by querying the Catalog table in the ReportServer database. This table holds some miscellaneous metadata for each report. Included in that table is a XML fragment that holds information about the report’s parameters.
Public Shared Function ParameterDisplay(ByVal globals As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals, ByVal parameters As Parameters) As String
Dim doc As New System.Xml.XmlDocument()
Dim xml As String = Nothing
Using connection As New System.Data.SqlClient.SqlConnection("YOUR_CONNECTION_STRING")
connection.Open()
Using command As New System.Data.SqlClient.SqlCommand()
command.Connection = connection
command.CommandText = "SELECT Parameter FROM Catalog WHERE Path=@Path"
command.CommandType = System.Data.CommandType.Text
command.Parameters.AddWithValue("Path", globals.ReportFolder & "/" & globals.ReportName)
xml = TryCast(command.ExecuteScalar(), String)
End Using
End Using
If String.IsNullOrEmpty(xml) Then
Return ""
End If
doc.LoadXml(xml)
Dim sb As New System.Text.StringBuilder()
For Each node As System.Xml.XmlNode In doc.SelectNodes("/Parameters/Parameter")
Dim parameterName As String = node.SelectSingleNode("Name").InnerText
Dim parameterPrompt As String = node.SelectSingleNode("Prompt").InnerText
sb.Append(parameterPrompt)
sb.Append(": ")
Dim parameter As Parameter = TryCast(parameters(parameterName), Parameter)
If parameter.IsMultiValue Then
Dim values() As Object = TryCast(parameter.Value, Object())
For i As Integer = 0 To values.Length - 1
If values(i) Is Nothing Then
sb.Append("NULL")
Else
sb.Append(values(i))
End If
If i <> values.Length - 1 Then
sb.Append(", ")
End If
Next i
sb.AppendLine()
Else
If parameter.Value Is Nothing Then
sb.AppendLine("NULL")
Else
sb.AppendLine(parameter.Value)
End If
End If
Next node
Return sb.ToString()
End Function
The key is that we use the Globals class to access the ReportFolder and ReportName. This allows us to build the full report path, which should correspond to what’s in the Catalog table.
An important note is that reports only reference the System assembly (along with the stock SSRS processing assemblies) by default. To use the above code you must first add references to the following two assemblies:
System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
The nice thing about this approach is that you have access to the friendly name for the parameter (the user Prompt).