how to get Median expression in paginated report(Calculate a Median in SSRS)

[Anonymous]

Hi Team,

iam trying to get Median of TAT_hours in paginated expession 

usually iam trying =Median(Fields!TAT_HOURS.Value) but getting an

error

(The 'Value' expression for the textrun 'TAT_HOURS.Paragraphs[0].TextRuns[0]' contains an error: [BC30451] 'Median' is not declared. It may be inaccessible due to its protection level.)

kindly check and provide the right one

[JohnDonnelly12]

Try this (it worked for me)

This custom code can be added to the report:

Public Shared Function Median(ByVal items As Object()) As Decimal
    If items Is Nothing Then
        Return Nothing
    End If

    Dim counter As Integer = items.Length
    If counter = 0 Then
        Return 0
    End If

    System.Array.Sort(items)

    If counter Mod 2 = 1 Then
        Return items(CInt((counter / 2) - 0.5))
    Else
        Dim FirstIndex As Integer = counter \ 2
        Dim SecondIndex As Integer = FirstIndex - 1

        Dim FirstValue As Integer = items(FirstIndex)
        Dim SecondValue As Integer = items(SecondIndex)

        Return (FirstValue + SecondValue) / 2
    End If
End Function

Which can then be called by using the following =Code.Median(Lookupset(Fields!Contract.Value, Fields!Contract.Value, Fields!Contract.Value, "DS_CallData_LKP"))

In this example the dataset "DS_CallData_LKP" is powering the entire report, but is being referenced back again to get list of values to be sorted for the median. Using a lookupset() instead of the hidden rows/columns method that is seen a lot helps keep the report simple for editing later down the line.

[Sahir_Maharaj]

Hello @Anonymous,

Can you please try this approach to use custom VB.NET code in SSRS to calculate the median:

Public Function GetMedian(ByVal items As Object()) As Decimal
    If items.Length = 0 Then
        Return Nothing
    End If

    Dim sortedList As List(Of Decimal) = New List(Of Decimal)
    For Each item As Object In items
        If IsNumeric(item) Then
            sortedList.Add(Convert.ToDecimal(item))
        End If
    Next

    sortedList.Sort()

    Dim count As Integer = sortedList.Count
    If count Mod 2 = 0 Then
        ' Even number of items - return average of middle two
        Return (sortedList(count \ 2 - 1) + sortedList(count \ 2)) / 2
    Else
        ' Odd number of items - return middle item
        Return sortedList(count \ 2)
    End If
End Function

In the expression where you want to display the median:

=Code.GetMedian(Fields!TAT_HOURS.Value)

Hope this helps.

[Anonymous]

Hi Sahir,

After trying above code getting an error like below 

There is an error on line 5 of custom code: [BC30002] Type 'List' is not defined.
----------------------------
The definition of the report '' is invalid.
----------------------------
An error occurred during local report processing.

[Anonymous · Accepted solution]

Thanks for the reply from Sahir_Maharaj , please allow me to provide another insight:

Hi, @Anonymous 

Based on my testing results, lists are not supported within custom functions in report builds.

Currently, a more effective solution I’ve found is to use SQL statements directly. For example:

The original data is as follows:

You can modify the SQL statement in the following location:

SELECT 
    *,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Table1.hour) OVER () AS Median_TAT_HOURS
FROM 
    Table1

Here are the final results:

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.