Calculate a Median in SSRS

Question

We need to be able to calculate the median value of a set of figures for a statistical return - specifically the median Answered figures per contract for a date range.

The data is stored in a shared dataset for use in Report Builder, and this shared dataset is used a number of contractual reports so updating it is not an option. The shared dataset being used ensures consistency between contractual reports, so must be used.

There are answers to this already (e.g. Find the median of a calculated field in SSRS 2012 & Use of 'median' function in calculated field in SSRS) but these require either hidden rows/columns or using a calculated field in a graph.

We need an answer that allows us to use shared datasets/stored procedures and calculate the median value in SSRS/Report Builder.

score 2 · Accepted Answer · answered May 07 '19 at 13:59

2

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!Answered.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.

answered May 07 '19 at 13:59

BishNaboB

1,047
1
12
25

I don't understand what the three parameters in the lookupset represent. I presume the first two (contract) are both referencing the field we are calculating a median from, but what is the third (answered)? – Astralbee Dec 16 '20 at 16:18
I remember a lookupset() as being "match this, with this, bring me this, from here". So in this instance I'm matching Contract in dataset A, with Contract in dataset B, and returning Answered from dataset B. We're calculating the median of Answered after matching Contract. – BishNaboB Dec 17 '20 at 23:00
With a median calculation, you just put everything in order and select the middle value. So I don't understand how to use the three parameters. The explanation of matching A with B and returning Answered doesn't make sense to me. You're simply putting in a list and asking to order it and provide the middle value. Cans someone explain these three parameters in a different way please? – lara Apr 26 '23 at 01:08
I think the `lookupset()` may be confusing matters. The code for the median requires an object (an array or a list, if that term works better for you) so I used `lookupset()` to get the data into that format. There are other options in SSRS - `CObj()`, or a multi-value parameter. The `lookupset()` option was easiest for me. – BishNaboB Apr 27 '23 at 06:12

Calculate a Median in SSRS

1 Answers1