SSRS Execution Service ParameterValue variable ind

Question

SSRS Execution Service ParameterValue variable index failing

I’m trying to use the SSRS Execution Service ParameterValue array without defining the number of indices. Microsoft’s example is this: http://technet.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.render.aspx ' Prepare report parameter. Dim parameters(2) As ParameterValue

    parameters(0) = New ParameterValue()
    parameters(0).Name = "EmpID"
    parameters(0).Value = "288"
    parameters(1) = New ParameterValue()
    parameters(1).Name = "ReportMonth"
    parameters(1).Value = "6" ' June
    parameters(2) = New ParameterValue()
    parameters(2).Name = "ReportYear"
    parameters(2).Value = "2004"

But I want to add a variable number of objects. I want to do something like this: ' Prepare report parameter. Dim parameters() As ParameterValue

        parameters(0) = New ParameterValue()
        parameters(0).Name = "EmpID"
        parameters(0).Value = "288"
        parameters(1) = New ParameterValue()
        parameters(1).Name = "ReportMonth"
        parameters(1).Value = "6" ' June
        parameters(2) = New ParameterValue()
        parameters(2).Name = "ReportYear"
        parameters(2).Value = "2004"

        For x As Integer = 0 To MyList.Count - 1
            ' Start
            Dim n As Integer = x + 3 ' Start adding values after the last entry
            parametersRdl(n) = New ParameterValue()
            parametersRdl(n).Name = "NameFromMyList"
            parametersRdl(n).Value = MyList(x)
        Next

Obviously I can’t define the number of indices in the array, because I don’t know who long MyList is. When I remove the number of indices I get this error: “NullReferenceException was unhandled by user code. Object reference is not set to an instance of an object.” Does anyone have experience with the SSRS ParameterValue object? OR am I doing something wrong with my array building? Hopefully I get an answer that works with ParameterValue.

Any help is appreciated, thanks!

Answer 1

This function creates a parameter list of SsrsExecutionService.ParameterValue values. It then adds parameter Names and Values of type ParameterValue() to the list. At the very end it does a List.ToArray to put the ParameterValue list into the ParameterValue array that SSRS accepts.

The purpose of this is to avoid creating a ParameterValue array and having to define the size of the index in the array. You will later pass this parameter array to reporting serives execution service: rs.SetExecutionParameters(parameters, "en-us")

This is also an example of how to send MDX parameters to SSRS.

    Public Function ToMDXParamArray(startDate As String, endDate As String, employeeNames As String, gender As String, ethnicity As String) As SsrsExecutionService.ParameterValue()

        ' The function was passed a comma delited text string of employee names. They need to be split up and added to the parameter array indivudually.
        ' If this were a SQL query the whole string could be passed to a single parameter.
        Dim employeeList() As String = employeeNames.Split(",")


        ' Create a parameter list to hold SsrsExecutionService.ParameterValue values
        Dim paramList As New List(Of SsrsExecutionService.ParameterValue)
        ' Define a single ParameterValue. In SSRS this has Name, Value, and Label fields. 
        Dim p As New SsrsExecutionService.ParameterValue()

        ' Create a new ParameterValue
        p = New SsrsExecutionService.ParameterValue()
        ' Assigne a name and value
        p.Name = "StartDate"
        p.Value = "[EmploymentDates].[YearMonthDate].[Month].&[" + startDate + "]"
        ' Add that ParameterValue to the parameter list
        paramList.Add(p)

        p = New SsrsExecutionService.ParameterValue()
        p.Name = "EndDate"
        p.Value = "[EmploymentDates].[YearMonthDate].[Month].&[" + endDate + "]"
        paramList.Add(p)

        p = New SsrsExecutionService.ParameterValue()
        p.Name = "Gender"
        p.Value = "[" + gender + "]"
        paramList.Add(p)

        p = New SsrsExecutionService.ParameterValue()
        p.Name = "Ethnicity"
        p.Value = ethnicity
        paramList.Add(p)

        ' Now add that list of employee names. For Analysis Services/MDX the names have to be added individually. For SQL you pass the entire string to a single parameter.
        ' This loop of an unknown number of employees in employeeList is exactly why you don't want to create a parametersRdl(50) As SsrsExecutionService.ParameterValue with a defined index size.
        For x As Integer = 0 To employeeList.Count - 1
            p = New SsrsExecutionService.ParameterValue()
            p.Name = "ProvidersProviderLocation"
            p.Value = "[Employees].[Employee Store].[Employees].&[" + employeeList(x) + "]"
            paramList.Add(p)
        Next

        ' Create the Execution Service Parameter Value object
        Dim parametersRdl() As SsrsExecutionService.ParameterValue
        ' Assigne the parameter list to the SSRS ParameterValue array
        parametersRdl = paramList.ToArray()

        ' Return the ParameterValue
        Return parametersRdl
    End Function

Once again I had to beat my head against a wall before figuring this out, I hope this helps others.

Answer 2

You're getting a null reference because your array has not been instantiated. You have to set the size of the array.

If you have access to the MyList object when declaring the array, you can do the following to create an array the size of the list plus three.

' Gives size of three + list count
Dim parameters(MyList.Count + 2) As ParameterValue 

If you only have access to the list at a later stage you can use the Resize function

Array.Resize(parameters, parameters.Length + MyList.Count)