SSRS Percentile function does not exist in 2008 R2 and previous SQL Server versions therefore you need to find alternative way to calculate percemtile. Below I will explain how you can do that in SSRS 2008 R2. Please be aware that I will use features that are 2008 R2 specific therefore you will be able to use it in future version of SQL Server like SSRS 2012 but you will have to adjust it to make it work properly in SSRS 2008 or SSRS 2005.
What is percentile function?
Let's start with explanation of percentile function. In the simplest from if you have a list of sorted values percentile takes the value from the percentage point you specified in the function arugment.
So let's explain percentile explanation using visual example.
Below we have 6 Employees (rows) with salary. I sorted the it using salary field.
You can see that first value is 0% and last values is 100% and values between have % according to their position. So if I want to use Percentile function I need to specify % I want to get so if I want Percentile 60% I would get value 30,000 in return. If I wanted Percentile 80% I would get 35,000
I hope the visual example helped to digest the idea of percentile function. We covered example where we specifed % that has a value next to it but what if we have a percentage between two values let say 50%?
This gets a little bit more interesting. If I want percentile 50% and 40% is 25,000 and 60% is 30,000 then 50% is equaly between 40% and 60% so percentile 50% is 27,500. Let's discuss Percentile 90%. 80% is 35,000 and 100% is 450,000. 90% is exactly between 80% and 90% so value exactly between 35,000 and 450,000 is our answer which is 242,500 which I calculated in the following way ((450k-35k)/2)+35k.
So the big question is how to work out 85%? 85% is not between 80% and 90%.
To solve this question I will help myself with a visual example that shows part of the original photo which is between 80% and 100%
so you can see that 90% is in the middle and we worked it out quite easily. 85% is 1/4 forth the way so the result is 138,750.
The point of this example is to visualize how we work out % that is between two values. So we don't value between (which would be simple) but we actually take accurate percentage between two values. Is that important? Yes it can be (on small sets of data). Let say you want 81% if we took a value between 80 and 100% we would get 242,500 but 81% is actually 55,750 so that is big difference in this case.
What is the purpose of Percentile
Percentile can be a very powerfull function in real life. Let's give two examples:
Suppose you want to find out what is the "standard" top salary in a bank but you want to exclude people that earn astronomic amounts of money so you can get top salary for "standard" employee. Banks employ a lot of people and only let say 2% get astronomic amounts so we can use Percentile with 95% to get salary for the 95% percentile of employees that is much more accurate to get top salary of a standard employee comparing to the person at the very top and it is also very easy to implement which saves time.
In the second example let's use Percentile 25%, 50% and 75%. Delivery company might be a good business to use it. Let says you are tasked with tracking deliviries. In order to monitor business performance you need to implement a way to measure business deliviries performance.
Let's say that you want 25% of orders to be delivered within 2 days 50% within 4 days and 75% within 6 days. This is quite detailed question and usually not easy to answer but we can use percentile function! and get order that is 25%, 50% and 75% percentile and compare it againts targets.
Percentile is a very powerful function that allows us to get more robusts results comparing to getting only average, min or max and it does it in a very easy to implement and understand way.
SSRS Percentile Function 2008 R2
Now I will show you a simple example how to implement percentile function in SSRS 2008 R2. I will use custom code and us the following code.
NOTE: I used visual studio to format the code. Unfortunatelly SSRS custom code is plain text.
The code below has 3 main parts:
1) values array list that will hold values which we will populate at run-time
2) AddValue function that will be used to add a value to the array at run-time
3) GetPercentile function will take array (that we will populate using AddValue). It will sort it and derive percentile value using fairly compherensive calculation.... (Wikipedia didn't help me to work out this one! It was easier to do it myself.)
Dim values As System.Collections.ArrayList
Function AddValue(ByVal newValue As Decimal) As Decimal
If (values Is Nothing) Then
values = New System.Collections.ArrayList()
End If
values.Add(newValue)
AddValue = values.Count
End Function
Function GetPercentile(ByVal Percentile As Decimal) As Decimal
Dim count As Integer = values.Count
Dim iLoop As Integer = 1
Dim OnePart As Decimal = 1 / (count - 1)
Dim PercentilePart As Decimal = Percentile / OnePart
If Int(PercentilePart) = PercentilePart Then
'is equal
GetPercentile = values(PercentilePart)
Else
'between two values
Dim Lower As Decimal = values(Int(PercentilePart))
Dim Higher As Decimal = values(Int(PercentilePart) + 1)
Dim LowerPercentage As Decimal = Int(PercentilePart) * OnePart
Dim HigherPercentage As Decimal = (Int(PercentilePart) + 1) * OnePart
GetPercentile = Lower + ((Higher - Lower) * ((Percentile - LowerPercentage) / (HigherPercentage - LowerPercentage)))
End If
End Function
I already have a dataset with table report item which has only "details row group". Go to details properties, select variables section (2008 R2 new feature) and add variable and add Name and in value type the following expression:
=Code.AddValue(Fields!TestValue.Value)
When the report runs this function will add every value that it gets from dataset into "array" that we declared in custom code that will be a container of our values which we will then sort and work out percentile.
See screenshot below.
Now that we have all values in an array; we can now add a percentile function to our report that will take use of it and return percentile result. See example below. In our case I specified 25%
Below is result for 25th Percentile
And below is result for 80th percentile. I included it here because the function uses slightly different calculation (simplier)
So that is it. I compared the results to Excel function and it seems I'm getting the same results but make sure you test it properly before you start using it.
I hope this SSRS tutorial will help you to create and use SSRS percentile function in your SSRS 2008 R2 reports
Take care
Emil
