SSRS Conditional Summing

Question

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

Answer 1

Found the answer....

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

Answer 2

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.