Sum with If Condition help

• Eliza

  SSC Eights!

  Points: 967

  More actions

  July 24, 2012 at 7:53 am

  #259367

  Hello,

  I am trying to Sum up some 1's and 0's based on whether the SUM of a field is greater than 75,

  In my report I wrote as an expression . . .

  =SUM (IIf( SUM(Fields!ActualAttendance.Value) / SUM(Fields!PossibleAttendance.Value) >=0.75, 1,0))

  Basically for each person I am trying to work out if there actual sales was greater than 75%, then add all these together to create a grand total number of people who met the 75% criteria. The nested criteria is the individual person, the outer sum is all added together. However this is not accepted by SQL server, it complains about aggregating an aggregation function.

  I cannot put the expression in the query its not an option, please trust me on that its hard enough to explain the above criteria without explaining why a query would not work. Basically I do not had access to the query, only the reports tool layout via reports builder 2.0. We cannot modify the query being used.

  Thanks

  Eliza

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  July 24, 2012 at 7:59 am

  #1516813

  Hi Eliza,

  Been a while since I've done this, but can you create the interior calculation as a calculated field in the report?

  Then do the exterior SUM on the calculated field?

  Cheers

  Gaz

  Edit: No, no you can't. Same aggregating an aggregate issue. :hehe: