Calculating grand totals from group totals in Reporting Services

I have some data grouped in a table by a certain criteria, and for each group it is computed an average —well, the real case is a bit more tricky— of the values from each of the detail rows that belong to that group. This average is shown in each group footer rows. Let's see this simple example:

What I want now is to show a grand total on the table footer. The grand total should be computed by adding each group's average (for instance, in this example the grand total should be 20 + 15 = 35). However, I can't nest aggregate functions. How can I do?

Thanks in advance.

From stackoverflow

• Unfortunately I'm away from my reporting development box at the moment but it's either:
  =(sum(Fields!Column1 + sum(Fields!Column2))
  OR
  =SUM(sum(Fields!Column1) + sum(Fields!Column2))

  I'm pretty sure it's the first of the 2.

  Auron : Thanks for your interest, but my simple example only refers to a column. The ideal solution should look like Sum(Avg(Fields!Column.Value, "Group")).

• You just need to add the SUM() function in the table footer which is the outer scope of both groups and will sum them all together. If you are summing on a condition, you may need to put that in there also.

• Reporting Services (2005, maybe 2008, too) don't support aggregates of aggregates directly.

  Use a custom report assembly, code references and named objects (Report Properties, References) that allow you to aggregate the values yourself.

  Your code could look like this:

  Public Sub New()
  
      m_valueTable = New DataTable(tableName:="DoubleValueList")
  
      'Type reference to System.Double
      Dim doubleType = Type.GetType(typeName:="System.Double")
  
      ' Add a single Double column to hold values
      m_valueTable.Columns.Add(columnName:="Value", type:=doubleType)
  
      ' Add aggregation column
      m_sumColumn = m_valueTable.Columns.Add(columnName:="Sum", type:=doubleType, expression:="Sum(Value)")
  End Sub
  Public Function Aggregate(ByVal value As Double) As Double
  
      ' Appends a row using a 1-element object array. 
      ' If there will be more than 1 column, more values need to be supplied respectively.
      m_valueTable.Rows.Add(value)
  
      Aggregate = value
  End Function
  Public ReadOnly Property Sum() As Double
      Get
  
          If 0 = m_valueTable.Rows.Count Then
              Sum = 0
          Else
              Sum = CDbl(m_valueTable.Rows(0)(m_sumColumn))
          End If
      End Get
  End Property
  

  Name you reference for example DoubleAggregator. Then replace the group expressions with "Code.DoubleAggregator.Aggregate(Avg(Fields!Column2.Value))" and the expression for Total with "Code.DoubleAggregator.Sum()".

  Auron : This could be interesting although I haven't tested it. Thanks for your answer, I put it as recommended until a better solution appear.

  : Please note that you cannot place the summary calculations in a table footer as its values are calculated statically "before" the table is processed. I'd recommend using another table of similar style with only static rows for the summary.

  NTDLS : @henbo - Does this work for you? Perhaps i do not understand your comment.

  keithwarren7 : FYI - Aggregates of Aggregates is supported in 2008 R2

• I am not able to achieve these group totals....The totals are coming only for the last page not for the first one.

  I'm using the custom sample code given above...Any other solution to achieve this???

  Auron : I'd suggest you to ask this in a totally new question to get more visibility. Copy the sample into your question and add a link to this question if necessary. I don't work any longer with Reporting Services, so I can't help. Sorry.