Aggregate of an aggregate function in SSRS

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

I am worse than a child when it comes to colours and no wonder, spend a lot of time on the aesthetics whenever I am preparing an Excel sheet or a PowerPoint presentation. For me, this is rather a way to indulge the child within me than to make it more presentable to the audience. The joy that you get when you arrive on that perfect colour for your headers or that perfect font to suit the mood or that perfect design template for your slides is something that can’t be expressed. Recently, I was preparing the sample data for a report and playing around with the colours in excel as usual when I noticed that there was an unexpected aggregation that would be required. Beads of sweat broke from my forehead as I contemplated going back to the user and saying that I might need some time to think whether this is possible. Let me give you an overview of the requirement. The measure (Order Count) comes in at a sub category level and the client wanted to see the average of the subcategories within a category, as well as an average of average as the grand total (refer below image for calculations)   I straightaway opened BIDS and decided to try it out for myself. I quickly made the dataset and designed the layout as shown below. On previewing the report, I got the following output I was almost giving that smug grin of mine thinking everything is fine, when I spotted that the Grand Total doesn’t match with the requirements. The totals were coming properly at the category level (which were just an average of the sub categories) but at the Grand Total level, instead of taking an average of the Categories [ (7585 + 7857 + 4082) / 3 ], it was still taking an average of the sub-categories [ (5212 + 7512 + 10030 + 6185 + 9528 + 3382 + 4781) / 7 ]. That is when I realized that what we need here is an average of an average at the grand total level. But SSRS 2008 and below versions doesn’t allow us to define an aggregate of an aggregate function. This was confirmed when I got the following error on replacing Avg(Fields!Order_Count.Value) by Avg(Avg(Fields!Order_Count.Value)) [rsAggregateofAggregate] The Value expression for the textrun ‘Order_Count9.Paragraphs[0].TextRuns[0]’ contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions. My next approach was to try using the Avg function on a textbox which would already have the value Avg(Fields!Order_Count.Value), something like Avg (ReportItems!Textbox21.Value). The result was again an error, not much different from the previous error [rsAggregateReportItemInBody] The Value expression for the textrun ‘Order_Count9.Paragraphs[0].TextRuns[0]’ uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers. That is when I decided to dig into the treasure troves of the MSDN forum, and unearthed this post by Jin Chen (MSDN Moderator) where he gives a sample custom code to achieve the functionality. Armed with a minorly tweaked version of this custom code, I followed the steps below to achieve my requirement:- 1) Go to design mode and right click on the area outside the report body. Select Report Properties 2) Select the Code tab and paste the following code there Dim public totalBalance as Integer
Dim public Cnt as Integer
Public Function AddTotal(ByVal balance AS Integer ) AS Integer
                totalBalance = totalBalance + balance
Cnt=Cnt+1
                return balance
 
End Function
 
Public Function GetTotal()
                return totalBalance/Cnt
End Function
  The AddTotal function basically adds all the values of a textbox and sets it to totalBalance variable. This function also counts the number of values. The GetTotal function returns the sum of the values by the count of the values which is basically the average of the textbox values. Once you have pasted the code, you can click OK. 3) Replace the expression at the Total level from =Avg(Fields!Order_Count.Value) to =Code.AddTotal(Avg(Fields!Order_Count.Value)) This is done so that when the average is being calculated at this textbox during report rendering, at the same time the sum and count of these averages will also be calculated. 4) Replace the expression at the Grand Total level from =Avg(Fields!Order_Count.Value) to =Code.GetTotal() This function will return the computed value, which would be the average of the average as we required. 5) Preview the report to confirm that the output matches with the requirement. Guess who is grinning smugly now? 😀