16

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))
Pedram
  • 6,256
  • 10
  • 65
  • 87
Whistler
  • 1,897
  • 4
  • 29
  • 50

2 Answers2

36

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:

enter image description here

enter image description here

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:

enter image description here

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.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • 1
    It is working very well, thank you. How would I use the sum of HourTarget for conditional formatting? When total of 7:00 is greater or equal Sum of HourTarget is green else red. I tried this one but I get error that it is not in the group. =IIF(Sum(Fields!HourCount.Value) >= Sum(Max(Fields!HourTarget.Value, "LineID")), "LimeGreen", "Red") – Whistler Feb 10 '14 at 13:42
  • 1
    Ok, I figure it out. If someone need it I have used: ReportItems![Text Box name].Value – Whistler Feb 10 '14 at 14:15
  • 1
    Cool, was just typing a reply. At the table header level your first approach should be OK (and seemed to work OK for me in a quick test) but yours is also a great way, especially if it works for you! – Ian Preston Feb 10 '14 at 14:16
  • 1
    Could you merge **Hour Target** rows for each group ? – ASalameh May 28 '18 at 08:47
1

( Sum(CDbl(Fields!Field_1.Value)) is working .

Image

4b0
  • 21,981
  • 30
  • 95
  • 142
Ayomi Nisansala
  • 19
  • 1