Aggregate of an aggregate function in SSRS

SSAS Dimension Attribute Properties : Part 2
June 23, 2010
London Ahoy!
July 27, 2010
Show all

Aggregate of an aggregate function in SSRS


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) Aggregate CustomCode  I straightaway opened BIDS and decided to try it out for myself. I quickly made the dataset and designed the layout as shown below. Design mode - layout On previewing the report, I got the following output Preview 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 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
                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. Custom code window 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. Preview result Guess who is grinning smugly now? 😀


  1. Anonymous says:


    I have a tablix as next:


    | YEAR2 |


    YEAR | =last(field!reservas.value) | <<<<—- brings me the last month of the year with data of YEAR2


    and this is the result that I wanna get

    —>year2 (dim)

    | 2009 2010..

    V 1983 1 12 <<<<—— =last(field!reservas.value)

    year (dim) 1984.. 5 25 <<<<——-=last(field!reservas.value)

    –> total 6 37

    the problem that I have is how to settle the header and footer groups to get the results, I hope you can help, thanks in advance!!


  2. Jason Thomas says:

    Hi Valen,

    Are you using SSRS 2005 or SSRS 2008?

    And also, did you try using the technique mentioned above? If I understood correctly, you just need to return the totalBalance instead of totalBalance/Cnt in the GetTotal(). Just try, else give me the details with the issue (and a link to a picture of the layout, you can upload it to some image hosting site and paste the link here)

  3. Andy Pineda says:

    Hi Jason, I have a question. What if I have various columns holding values and I need to get a total at the bottom? My issue is that my sp is bringing in say, 100 values, but in my report i am rendering the top 25 values….however, my bottom total is totaling my 100 values, and my totals are obviously off. With your previous mentioned solution, I get a grand total of all of my columns…is there a way i could break that up into each individual column to have different totals at the bottom?

  4. Hey Jason,

    Thanks for your help but the code doesn't work if the data is in multiple pages, it sums the values separately for each page even though they belong to the same group. Could you please help me with this. Am using SSRS 2008 R2

  5. Sue Hocking says:

    i am trying to total the some columns that are in a matrix, these columns, towns, however the total are are expressions, sums of report items, I have tried in scope with group, and matrix but i keep getting the either one calculation or 0 i can send you a picture however it doesnt allow it here

    thanks in advance


  6. Anita says:

    All these years later and it’s my turn to grin smugly! I had 2 columns as well and basically just duplicated the code with different function names and variables, calling the one I needed in the column I needed. (Not an average, just a simple sum.)

    Dim public TotalGross as Decimal
    Dim public TotalNet as Decimal
    Public Function AddTotalGross(ByVal Gross AS Decimal ) AS Decimal
    TotalGross = TotalGross + Gross

    return Gross

    End Function

    Public Function AddTotalNet(ByVal Net AS Decimal ) AS Decimal
    TotalNet = TotalNet + Net

    return Net

    End Function

    Public Function GetTotalGross()
    return TotalGross
    End Function

    Public Function GetTotalNet()
    return TotalNet
    End Function

  7. Doreen says:

    This code solved my problem but I had to modify I a bit because it was rounding up (105.5 was returning 106) when precision was needed. I didn’t need the averaging, only need the sum of sums in a group. I replaced decimal data types wherever integer was used and this eliminated the rounding issue – at least for me it did. Thank you Jason, brilliant!

Leave a Reply

Your email address will not be published. Required fields are marked *