CODEWITHSUNDEEP
reporting-servicesssrs-2012medianreportbuilder3.0
BishNaboB
BishNaboB

Reputation: 1070

Calculate a Median in SSRS

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.

Upvotes: 3

Views: 4547

Answers (1)

BishNaboB
BishNaboB

Reputation: 1070

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.

Upvotes: 3

Related Questions