SSRS Conditional Summing

24
votes

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

2
reporting-servicesconditionalsum
Why CDbl is required in this scenario? - user1074910
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

2 Answers

37
votes

Found the answer....

=SUM(IIF(Fields!PART_TYPE.Value ="P",CDbl(Fields!EXT_QTY.Value * Fields!PRICE.Value), CDbl(0.0)))
15
votes

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.