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)))