ssrs median function 2008 R2

SSRS median function does not exist in 2008 R2 and previous SQL Server versions therefore you need to find alternative way to calculate median. 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 in future version like SSRS 2012 but you will have to adjust it to make it work properly in SSRS 2008 or SSRS 2005.

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.

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()

    End If

    values.Add(newValue)

    AddValue = values.Count

  End Function

  Function GetMedian() As Decimal

    Dim count As Integer = values.Count

    If (count > 0) Then

      values.Sort()

      If count Mod 2 = 1 Then

        GetMedian = values((count - 1) / 2)

      Else

        GetMedian = (values((count / 2) - 1) + values((count / 2))) / 2

      End If

    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 derive middle value.

See screenshot below.

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

Take care

Emil