Wednesday, July 23, 2008

Calculate a median on a group in SSRS

Today i needed to do something that turned out to be impossible to do in the conventional manner. I was doing a report on some items that are measured in days, each row in the report was actually a group, and i needed to calculate a median for that group. Step by step, here is how i did it.

First, here is the report layout i started with:





as you can see i have two row groups, with the detail row of the second group hidden (because i don't want to list all the details, there are hundreds of them).

Initially this seemed quite simple - for each detail row i would call a custom function which would add the value to an array, and then in the group footer i could just call another custom function called CalculateMedian() to get the median of the group. But the problem is, when i called CalculateMedian(), my array was empty, so i had no values to calculate with. I double checked, and yes the AddToMedian() function was being called for every detail row, so wtf? After some digging around and some Googling, i found one or two blog posts that mention group header and footer expressions get evaluated before the group detail expressions - this meant i was trying to calculate the median before the values had even been added to the array! What the???!!!! Who the fuck came up with that brilliant design, and why? I can understand processing group headers before the details (for sorting puroses, etc), but surely footers should be done last? I mean, for 99.9% of cases, footers will contain aggregate or summary information, right? (I don't care about the other 0.1% of cases and whatever they are trying to put in the footer, if they want to do non-standard stuff then they should be prepared to feel the pain of getting it to work).

So, i couldn't use the group footer to show my summary information, a different approach was required. To achieve my median calculation, three things need to happen:
- at the start of each category group the array used to accumulate the values needed to be emptied.
- i needed to iterate the detail rows of each category group, as there is no other way of passing the group values through to a custom function.
- at the end of each category group i need to calculate and display the median value.

To begin with, i replaced the table that had the division and category row groups on it with a list container for the division group (call it the DivisionList). Above that i put a rectangle with a textbox for each column title, this replicates what was in the heading of the table:





After that, i nest another list inside the DivisionList container, this has a grouping set to group by category and is called the CategoryList:





Note that immediately inside each list container i have a textbox which shows the name of the grouped item. The CategoryList is now going to perform the same function as each row did in the original table, so i add a few more textboxes to hold the values for that "row":





Note that i have already added the expressions, the important one is the median textbox, which has the expression:

=Code.CalculateMedian()

So this leaves the big question: i have the required grouping set up, and my aggregate/summary expressions in place, but how am i going to achieve objectives 1 and 2 from above, which was to reset the array holding the values, and to iterate rows in the category group so that i can place the individual values into the array the median is calculated from?

Hmmm, how can we iterate the values? I know - let's bring back the table, just give it one column:





if you are paying attention, you will see that it is inside the CategoryList container, with all the textboxes. Check out the expression, as the values get placed into the table rows they also get lodged in the array that is used for the median calculation. That's objective #2 resolved, now for objective #1, resetting the array. It's time for sly trick #1, using the knowledge that the table header expressions are processed before the table detail expressions, we can do this:





see what i did? I used an expression in the table header to reset the array. I also set the visibility of the table to hidden - i don't want this to show on the rendered report, remember? Sweet, now i have just one little detail left to take care of - how can i ensure that the table rows are filled before i call CalculateMedian() from my median textbox?

To achieve this, i employ sly trick #2: reports are rendered top to bottom, left to right. I've not seen this documented anywhere, it's just what i've observed, i've depended upon that behaviour in the past and it hasn't let me down yet. So, make the table 0.1cm wide, and make it the same height as the textboxes that are in the CategoryList container:





see that orange kind of spot on the report there? I added some colour to the table just so you could see what i've done. Now that it is resized, i move it to the left and above of the category label (IOW move it to location 0,0 of the CategoryList container, look for the orange spot). This ensures that it gets filled/rendered before the rest of the textboxes in that list container:





And that's all. Now, i just adjust the height of my list containers, and i run the report. This is what the final result looks like:





As you can see, the final result is almost the same as if i had used a table, but with this method i get to calculate the median on grouped rows - what a mission that was :(

For those of you who need it, here is the code for accumulating the median values and calculating them. It's pretty sandard stuff, nothing too exciting.

Dim Public Shared MedianArray(0) As Integer

Public Function ResetMedian()
ReDim MedianArray(0)
End Function

Public Function AddToMedian(fieldValue As Integer)
Dim i As Integer
i = UBound(MedianArray) + 1
ReDim Preserve MedianArray(i)
MedianArray(i) = fieldValue
AddToMedian = fieldValue
End Function

Public Function CalculateMedian() as String
Dim arraySize as Integer
Dim ii as Integer
Dim jj As Integer
Dim itemMoved As Boolean
Dim temp As Integer


'sort it & calculate it
arraySize = UBound(MedianArray)
If arraySize = 1 Then
CalculateMedian = CStr( MedianArray(0) )
Exit Function
Else If arraySize > 1 Then
For ii = 0 To arraySize - 1
itemMoved = false
For jj = LBound(MedianArray) To UBound(MedianArray) - 1
If MedianArray(jj) > MedianArray(jj + 1)
temp = MedianArray(jj)
MedianArray(jj) = MedianArray(jj + 1)
MedianArray(jj + 1) = temp
itemMoved = True
End If
Next
If itemMoved = False Then Exit For
Next

'calculate it
If arraySize Mod 2 = 0 Then
'average the two middle values
CalculateMedian = CStr( (MedianArray(arraySize / 2) + MedianArray((arraySize / 2) + 1)) / 2)
Else
'get the middle value
CalculateMedian = CStr( MedianArray(Floor((arraySize / 2)) + 1) )
End If
End If
End Function









keywords: SSRS, SSRS2005, median, row group, calculate, custom aggregate

6 comments:

Anonymous said...

Oh my GOD - you are my new best friend! It worked GREAT! The only thing I had to do was tell it what to do if there is only 1 number. Thank you so much for posting this.

shane said...

No worries, glad to help :)
It took a couple of days of screwing around and a couple of hacks to get it to work, but to my knowledge this is still the only way to do it in this situation. Just remember, i haven't tested this is SSRS2008 yet.

Alex said...

it won't work in SSRS2008 when the fields are hidden because hidden fields are not evaluated any more in SSRS2008, otherwise it should work.

Reg.

Alex

Anonymous said...

Great work only had to change array value to pick when there is oner rec

'sort it & calculate it
arraySize = UBound(MedianArray)
If arraySize = 1 Then
CalculateMedian = CStr( MedianArray( 1 ) )
Exit Function

Anonymous said...

Take a look at http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

I used this article and that one to get it to work in SSRS 2008, as I had the issue of the array not being cleared either, therefore I added the code I needed in this article to my custom code and called it in the Group where I had called the GetMedian() function.

Anonymous said...

Here it is 2013 and this is one of the very few articles out there that explain this. Thanks so much!

My only comment is that if mod 2 = 0 then that means it is an even number of objects correct? If so I think the logic is reversed on the IIF statement.

I have this working on SSRS 2008 r2 with a hidden details group and corresponding hidden tablix row.