Calculate Percentile in SSRS

Question

We need to calculate a percentile (95th and 99th) in an SSRS report based on a shared dataset which return aggregated data by 15 minute interval. The percentile needs to be for the day as a whole, but per contract.

For arguments sake, lets say this is our data:

select
    'Test' as [ContractName],
    1 as [Value]

union all

select
    'Test' as [ContractName],
    3 as [Value]

union all

select
    'Test' as [ContractName],
    4 as [Value]

union all

select
    'Test' as [ContractName],
    6 as [Value]

union all

select
    'Test' as [ContractName],
    7 as [Value]

union all

select
    'Test' as [ContractName],
    36 as [Value]

union all

select
    'Test' as [ContractName],
    56 as [Value]

union all

select
    'Test' as [ContractName],
    798 as [Value]

union all

select
    'Test' as [ContractName],
    324 as [Value]

union all

select
    'Test' as [ContractName],
    456 as [Value]

union all

select
    'Test' as [ContractName],
    657 as [Value]

union all

select
    'Test' as [ContractName],
    658 as [Value]

This is almost sorted (it was when I created it) but the data itself wouldn't be in the real-world application.

I need to be able to get the percentile from this dataset without altering the SQL as it's a shared dataset used for contractual reporting and cannot be changed. The shared dataset is used to ensure consistency across the entire suite of reports. The solution must therefore be in the tablix in the final report output.

I've looked at SSRS expression - query from dataset, with group and noticed that it has the same basic usage as this Find the median of a calculated field in SSRS 2012, which comes from https://blogs.msdn.microsoft.com/robertbruckner/2008/07/21/using-group-variables-in-reporting-services-2008-for-custom-aggregation/ and requires a number of hidden rows and group variables which are not easily maintained/edited at a later date.

score 1 · Accepted Answer · answered May 07 '19 at 16:17

The following custom code can be added to the report:

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

    Dim counter as Decimal = items.Length
    If counter = 0 Then
        Return 0
    End If

    System.Array.Sort(items)

    Dim Position As Decimal = (k * (counter - 1))

    Dim FirstIndex As Integer = Floor(Position)
    Dim SecondIndex As Integer = Ceiling(Position)

    Dim Remainder As Decimal = Position - FirstIndex

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


    Return (FirstValue + (Remainder * (SecondValue - FirstValue)))

End Function

Which can be called with a lookupset() in the expression like this:

=Code.Centile(lookupset(Fields!ContractName.Value, Fields!ContractName.Value, Fields!Value.Value, "DS_Centile_LKP"), 0.95)

This calls back on itself to get the values from the same dataset by looking up against the ContractName field. The lookupset() returns an array to be sorted in this function, rather than having variables and things all over the place - this is a simple function in the tablix textbox where the value is displayed. It also allows different values for which percentile you want to use - in the example above it's 95th, so 0.95.

Calculate Percentile in SSRS

1 Answers1