topic Re: how to get Median expression in paginated report(Calculate a Median in SSRS) in DAX Commands and Tips

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

Anonymous — Wed, 06 Nov 2024 10:32:34 GMT

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

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

Sahir_Maharaj — Wed, 06 Nov 2024 22:23:14 GMT

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.

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

Anonymous — Thu, 07 Nov 2024 13:33:21 GMT

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.

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

Anonymous — Mon, 11 Nov 2024 07:16:54 GMT

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.

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

JohnDonnelly12 — Thu, 13 Mar 2025 14:43:12 GMT

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.