This article requires SSRS 2008 R2 or higher version (or Report Builder 3.0)
What is median function?
Let's start with explanation of median function. In the simplest from if you have a list of values median takes the value in the middle. Below I will give two scenarios that you should now:
Let say we want to take Median using employee salary field. We have 5 employees with salary between 15k and 450k. In order to get median we need to sort salary and if we have 5 values then the 3rd value is in the middle. See below example.
Our median from the above example is 25,000
There is one exception to this rule. What if we two employees are in the middle? So let say we employeed another person with salary 30,000. We sorted the values and we get two employee in the middle. See below.
In the above example we have two employees in the middle so in order to get median we need to sum both values 25,000 + 30,000 = 55,000 and divide by 2 which gives us median = 27,500
Average vs Median
People often think of average as Sum of values / number of values (SUM/COUNT). Actually Median is another type of average.... anyway so why would we not want to use average? In the example above with 5 Employees earns 545,000 divided by 5 employees gives us average of 109,000. Very high average but if you look at data you will notice that you get very high average because the boss earnes 450,000.
So if we ask a question what is the typical salary of an employee we cannot give average but we can give median which in case of 5 employees is 25,000 (x4 less than "standard" average!). That is very big difference.
To summarize average works well if are values are distributed evenly but if you get outliers (outliers = values largely outside of typical range) than you might want to use median instead.
SSRS Median Function 2008 R2
Now I will show you a simple example how to implement median in SSRS 2008 R2. I will use custom code and us the following code.
Where is custom code? If you are using Visual Studio (BIDS or SSDT BI) than go to Report Menu // Report Properties // Code (Section on the left) and paste the code from below.
Using Report Builder 3.0? Check Microsoft website.
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) GetMedian function will take array (that we will populate using AddValue). It will sort it and derive median value.
Dim values As System.Collections.ArrayList
Function AddValue(ByVal newValue As Decimal) As Decimal
If (values Is Nothing) Then
values = New System.Collections.ArrayList()
AddValue = values.Count
Function GetMedian() As Decimal
Dim count As Integer = values.Count
If (count > 0) Then
If count Mod 2 = 1 Then
GetMedian = values((count - 1) / 2)
GetMedian = (values((count / 2) - 1) + values((count / 2))) / 2
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:
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 derive middle value.
See screenshot below.
Make sure you chage Test Value field name in the expression above with the field name you want to use (case sensitive).
Now that we have all values in an array; we can now add a function to our report that will take use of it and return median. See example below
Below is result for 5 employees
and below is result for 6 employees
I hope this SSRS tutorial will help you to create and use SSRS median function in your SSRS 2008 R2 reports