How to sum the Group totals only?

Question

I'm trying to sum only group HourTarget totals.

          7:00     8:00    HourTarget    
Line 1    2715     1008      3224
  A       2307     1008      3224 
  B       408        0       3224
Line 2    2308     2432      2656
  A       2308     2432      2656
Line 3    2318     1622      2800
  A       345      1258      2800
  B       762        0       2800
  C       1211     364       2800

I'm trying to achieve 8680 as a result of sum of HourTarget. But I'm getting 17504. It is because HourTarget in a database table is record for every single product running on this line, but the target is related to the line and not the product. How can I sum only the Group total?

Something like this is not working:

=Sum(Max(Fields!HourTarget.Value))

Answer 1

Since you are on SSRS 2008R2, you can use the aggregate of aggregate functionality that was added in that version.

You were on the right track; you just need to add a Scope value to your expression.

I'm using a version of your data, and have constructed a simple tablix:

Note that I have created a group called Line.

To get the Sum of the Max HourTarget column, use the expression:

=Sum(Max(Fields!HourTarget.Value,  "Line"))

This works out the Max for each Line group, then takes the Sum of these.

Now we have your required value:

Prior to SSRS 2008R2 there was no easy way to do this; typically one would add an extra column to the DataSet with the pre-aggregated value to display in the report.