Error handler for sum in ssrs

Question

I have a cell which is having sum of my row values. that is =sum(Fields!amount.value). sometimes when amount contains non-float values the sum cell will result in "#error". I need to change the color of cell when the cell is in "#error" condition. How I can achieve it?

Answer 1

I think I would use IsNumeric() to inspect the values and see if you have any that will cause the error. You can just use Iif() to turn the boolean into a 1 or 0 then Sum() that up to see if you have any, and enter that as an expression for the BackgroundColor field in the Textbox properties. Something like this should do it:

=Iif(Sum(Iif(IsNumeric(Fields!amount.value), 0, 1)) > 0, "Red", "Transparent")

That's saying..."inspect the amount field, if it's not numeric return a 1. Add all those up, and if the total is more than 0 (meaning at least one of the values is not numeric, and thus you'll get #Error) turn the field red".