Accessing Parameter Collection in SSRS Custom Code to get Parameter Name/Value Pairs

Question

I have an SSRS report requirement to generate json string through custom code by concatenating all the report Parameter name and value pairs. To achieve this I can explicitly access each report parameters and concatenate to generate the expected text. But , I am trying to find if there is anyway to loop through the parameter collection and generate it dynamically , so that there is no need to update the function whenever a new parameter is added. Thank you!!

Answer 1

OK, so due to the fact that the parameters collection is not very well supported via custom code, this will only work once the report is deplyed to the server. This makes it tricky to debug but we can work around that by hardcoding a parameter whilst we test.

This answer might look long but in fact it's pretty quick to do.

To get started Create a report and add your parameters

In my sample report I had two parameters,

1. CountryID (text - multivalue)
2. PeriodID (integer - single value)

Deploy the report now even though it's not finished we need to deploy now for the rest to work.

So the first things we need to do is get a list of parameters. We can do this if we know the report's full path. We will hardcode this value for now but make it dynamic before we finish.

Create a dataset called dsParameters and set the query to the following.

DECLARE @pNameList varchar(1000) =''
SELECT  @pNameList = eachParam.value('Name[1]', 'VARCHAR(250)') +'|' + eachParam.value('Type[1]', 'VARCHAR(250)') + CASE @pNameList WHEN '' THEN '' ELSE ',' + @pNameList END 
    FROM (
            SELECT CONVERT(XML, c.Parameter) AS pxml 
                FROM  ReportServer.dbo.Catalog c  
                WHERE c.[Path] = @ReportPath
            ) a  
            CROSS APPLY pxml.nodes('//Parameters/Parameter') ( eachParam )
SELECT @pNameList  as pNameList

This will return someting like

PeriodID|Integer,CountryID|String

Now right-click the dataset, choose "dataset properties" then "Parameters".

Set the @ReportPath parameter value to the path and report name of your report. You can get this ReportServer.dbo.catalog in the Path column, it will look something like ="/Sales Reports Folder/My Sales Report" . Note the forward slashes.

We will come back to the hardcoded value later once it's all working.

Next, go to the report's properties and click the Code tab.

Paste in the following two functions.

Public Function GetParameterValues(ByVal parameter as Parameter, ByVal pType as string)  as String  
   Dim s as String = ":["
   If parameter.IsMultiValue then  
      For i as integer = 0 to parameter.Count-1  
         if i >0 then 
            s = s + "," 
         end if
         if pType = "String" then
            s = s + """" + CStr(parameter.Value(i)) + """"
         else
            s = s + CStr(parameter.Value(i)) 
         end if
      Next  
   Else  
      s = s + CStr(parameter.Value)  
   End If  
   Return s + "]" 
End Function  


Public Function GetJSON(ByVal parameters as Parameters, pNameTypeList as String) as string
' pass in a list of parameter names
' for each name GetParameterValues
'     append the result to json txt

    Dim pList() AS String = Split(pNameTypeList, ",")
    Dim pParts() AS String
    Dim i as Integer
    Dim pName as string
    Dim pType as string
    Dim json as String ="{"

    While i <= pList.Length - 1
        pParts = Split(pList(i), "|")
        pName = pParts(0)
        pType = pParts(1)
         if i >0 then 
            json = json + "," 
         end if        
        json = json & """" & pName & """" 
        json &= GetParameterValues(parameters(pName), pType) 
        json = json & vbcrlf
        i += 1
    End While

    json = json & "}"

    Return json
End Function

The first function accepts a parameter object and data type and loops thru the parameter values to return a single line such as "CountryID":["89","94"]

The second function takes the parameters collection and a list of parameter names and types in the form Name1|Type1,Name2|Type1. It starts with the json "header", repeatdly calls the first function adding comma's as required and then closes the json.

The output will be something like this...

{"PeriodID":[2020300]
,"CountryID":["89","94"]
}

NOTE I have only defined quoting for the String type, you may need to adjust to suit your needs.

Finally (almost) create a textbox and set the value expression to

=Code.GetJSON(Parameters, 
            First(Fields!pNameList.Value, "dsParameters")
            )

Here's the final report output...

FINALLY We need to make that dataset parameter dynamic. Go back to the dataset's parameters and set the @ReportPath parameter value to

=Globals!ReportFolder & "/" & Globals!ReportName

That's it.