26

I have an SSRS report that displays several pages of rows. In each row is a "TYPE" field. In that TYPE field there is either an "M" for the value or a "P" for the value. At the end of the report I want to summ up all the price values for the "P" TYPES. I tried this but it prioduced an #Error:

=Sum(iif(Fields!TYPE.Value = "P",Fields!EXT_QTY.Value * Fields!PRICE.Value ,0))

this summed all rows

=iif(Fields!PART_TYPE.Value = "P" ,  Sum(Fields!EXT_QTY.Value * Fields!PRICE.Value ),  0 )

I'm sure this is do-able. Any ideas? Thanks

Mark
  • 8,046
  • 15
  • 48
  • 78
MikeTWebb
  • 9,149
  • 25
  • 93
  • 132
  • Why CDbl is required in this scenario? –  Dec 01 '11 at 07:21
  • 5
    in OP's scenario `Sum(Fields!EXT_QTY.Value * Fields!PRICE.Value )` was producing numbers with double and/or decimal type and in SSRS when you perform SUM , it expects to SUM similar types and `0` is integer. It was failing because of that. So converting both true and false part of IIF statement to same data type resolves the error. – N30 Aug 14 '12 at 17:28

2 Answers2

39

Found the answer....

=SUM(IIF(Fields!PART_TYPE.Value ="P",CDbl(Fields!EXT_QTY.Value * Fields!PRICE.Value), CDbl(0.0)))
Pedram
  • 6,256
  • 10
  • 65
  • 87
MikeTWebb
  • 9,149
  • 25
  • 93
  • 132
15

The SUM fails due to type comparison - you can't Sum values of different types, being the expression (probably a Double) with 0, an Integer. MikeTWebb's answer does explicit type conversion to get around this error. This is fine for this specific example, being a Sum, however this doesn't produce an accurate result if you want an average (being Sum / Count) of the values where the Type is P. That is because 0 is a value and would be included in the averaging calculation when you actually want those values excluded from the calculation.

Another option is to use Nothing instead of 0:

=Sum(IIF(Fields!TYPE.Value = "P", Fields!EXT_QTY.Value * Fields!PRICE.Value, Nothing))

This solves the type comparison error without needing explicit typecasting and is a better solution when you are using aggregations where whether the value exists or not is significant to the result, like Average.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • 1
    This was perfect. I definitely think that the use of Nothing is the way to go as a "rule of thumb" because of calculations like Average. – Pete Apr 12 '16 at 14:23
  • 1
    Yay! This was exactly what I needed. Still don't get who thought of 'Nothing' beeing a good keyword for null though. – Akaino Mar 14 '17 at 09:15