SSRS Conditional Summing

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.


Found the answer....

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