Search code examples
sql-serverreporting-servicesreportingservices-2005

Sum of group items SSRS 2005

Is there a way to sum the numeric values of a group?

My report is structured in a couple of groups like so

Group1
    Group2
        Group3
            Data

The names of the groups would look look like 

League
   IsHomeTeam
      Team
          Player

So an example of the report structure would be

NHL
    True
        NJ Devils
            Martin Brodeur    Stat1    Stat2
            Patrick Elias     Stat1    Stat2
            ....

What I'd like to do is get the Max of Stat1 for group3 (Team), then get the sum of that number for group1 (League).

This is similar to another question here but I'm asking for SSRS 2005.
Summing Group Items in SSRS 2008 R2

When I tried using Sum(Max(Fields.Stat1.Value,"Team"),"League") I got an error saying "Aggregate functions cannot be nested inside other aggregate functions".

Does anyone have another way of doing this?

Solution

  • The approach described at http://www.sqljason.com/2010/07/aggregate-of-aggregate-function-in-ssrs.html should work for 2005.

    The trick is to write some custom code that keeps track of the running total as a variable, and then call that code once per group to add to the total. Calling the code can be done by placing a reference within a cell to the code, such as in that article =Code.AddTotal(Avg(Fields!Order_Count.Value)