0

I have a SQL Server Reporting Services report in which I calculate the median using an ArrayList in a custom code block as median is not a built in function in SSRS. The median and average are displayed in a column chart. The report includes a drilldown to a detail report. If I drill down to the detail report and then hit the back arrow in the browser to navigate back to the master report, the median on the master report is not being calculated. That column of the column chart is empty while the average is displayed as it should.

Does anyone have any thoughts on why this is happening or how I can fix it?

The code is below. AddValue() first creates a new SortedList of it doesn't already exist. It then adds the newValue to an ArrayList keyed by the string passed to the function, creating one if it didn't previously exist. Each ArrayList represents a different set of values for which we would want to calculate the median.

GetMedian() calculates the median for the ArrayList identified by the passed string. GetMedian() is used in the value for the column in the chart.

Public Dim ClearanceList As System.Collections.SortedList

Function AddValue(ByVal whichList As String, ByVal newValue As Integer) As Decimal
    Dim thisList As System.Collections.ArrayList

    If (ClearanceList is Nothing) Then
        ClearanceList = New System.Collections.SortedList
    End If

    If (ClearanceList.ContainsKey(whichList)) Then
        'Get a reference to the desired ArrayList and add the new value to it.
        thisList = ClearanceList.GetByIndex(ClearanceList.IndexOfKey(whichList))
    Else
        thisList = New System.Collections.ArrayList()
        ' Create a new element in the SortedList
        ClearanceList.Add(whichList, thisList)
    End If 

    thisList.Add(newValue)
    AddValue = thisList.Count
End Function

Function GetMedian(ByVal whichList As String) As Decimal
    Dim thisList As System.Collections.ArrayList
    Dim count As Integer 

    thisList = ClearanceList.GetByIndex(ClearanceList.IndexOfKey(whichList))
    count = thisList.count

    If (count > 0) Then
        thisList.Sort()

        If count Mod 2 = 1 Then
            GetMedian = thisList((count - 1) / 2)
        Else
            GetMedian = (thisList((count / 2) - 1) + thisList((count / 2))) / 2
        End If
    Else
        GetMedian = -1
    End If
End Function
Rainman
  • 101
  • 1
  • 1
  • 6
  • Can you post your function code and the version of ssrs you are using? – Bave Oct 04 '17 at 17:40
  • Also, you should use the 'back to parent' button on the toolbar to navigate back rather than the browsers back button. – Alan Schofield Oct 04 '17 at 18:56
  • This is SSRS 2016. The behaviour occurs whether I use the built in 'Go Back to the Parent Report' button built into the SSRS toolbar or the browser back button. It occurs both in the Report Portal as well as the SharePoint web part page that hosts it for the majority of the users. Therefore we can eliminate SharePoint as a possible point of failure. – Rainman Oct 04 '17 at 19:56

0 Answers0