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.
To get full access to this page
Become a member or Sign in
Membership is just $3.99/month