How to reference SSRS report parameter by name

748 views Asked by At

I have the following code that I'm attempting to programmatically create an xml report with through ReportViewer.

Dim outputPath As String = "C:\backup\test.xml"
Dim reportViewer As New ReportViewer()
Dim serverReport As ServerReport = reportViewer.ServerReport()

reportViewer.ServerReport.ReportPath = "/<path>/<report>"
reportViewer.ServerReport.ReportServerUrl = New Uri("http://<ip>/reportserver")
reportViewer.ProcessingMode = ProcessingMode.Local

reportViewer.ServerReport.ReportServerCredentials.NetworkCredentials = New System.Net.NetworkCredential("<user>", "<pwd>")

Dim parameters As New List(Of ReportParameter)

parameters.Add(New ReportParameter("LocationLocationParent", "[Location].[Location Parent].&[2]"))
parameters.Add(New ReportParameter("CalendarYear", "[Calendar].[Year].&[2014-01-01T00:00:00]"))
parameters.Add(New ReportParameter("StaffStaffName", "[Staff].[Staff Name].&[Bob Smith]"))

Try
    serverReport.SetParameters(parameters)

    Dim mimeType As String = ""
    Dim encoding As String = ""
    Dim extension As String = ""
    Dim streams As String() = Nothing
    Dim warnings As Warning() = Nothing

    Dim xmlBytes As Byte() = serverReport.Render("XML", String.Empty, mimeType, encoding, extension, streams, warnings)

    Using fs As FileStream = New FileStream(outputPath, FileMode.Create)
        fs.Write(xmlBytes, 0, xmlBytes.Length)
        fs.Close()
    End Using

Catch reportEx As ReportServerException
    Debug.Print(reportEx.Message)
End Try

where I'm setting the parameter for LocationLocationParent, it is not allowing me to set the value by name, only by some type of index each value in the list has. The drop down list on the SSRS report itself is in a tree-like structure, something like this:

Parent1
    Parent2
        Parent4
            Child1
            Child2
        Parent5
            Child3
            Child4
        Child5
    Parent3
        Child5
        Child6

This list is not a true tree but just viewed like one. It is a multi-select list of checkbox items. I cannot set the parameter to value "Child3" for example - only by its index, but there are 100s of values in the list on the Report Window. If I set it like this: "[Location].[Location Parent].&[Child3]" I get the following exception

This report requires a default or user-defined value for the report parameter 'LocationLocationParent'. To run or subscribe to this report, you must provide a parameter value. (rsReportParameterValueNotSet)

Note that the StaffStaffName parameter is not formed in a tree-like structure, just a list, so I am able to set that parameter correctly.

How can I set to the name of the value I want for LocationLocationParent?

0

There are 0 answers