I've got a report footer that sums up the rows of data in a column to a total. However, some of the data in the rows, instead of zero, is non-numeric. I thought the expression below would reset the value to 0 in the sum funciton, but it's not working- the cell has the #ERROR value, and in the debugger, I see the [rsAggregateOfNonNumericData] error. Any ideas on what I can do with this expression to get the data to sum correctly? I want to leave the rows as blank (or whatever they are) instead of replacing them with zeros (which I think the sum would work for). Here's the expression: =Sum(iif(IsNumeric(Fields!MyField.Value), Fields!MyField.Value, 0 )) Here's the error in the debugger: [rsAggregateOfNonNumericData] The Value expression for the textbox textbox55 uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

Do you have nulls? Can you try this: Sum(iif(IsNumeric(Fields!MyField.Value), CDbl(Fields!MyField.Value),CDbl(0) )) Thanks.

Not sure if they're nulls or blank spaces. I tried your suggestion it also resulted in the #ERROR, however in the output window, I now get: Input string was not in a correct format.

Could you try replacing Nulls and blanks in your source data or in formula above with zero?

It's strange- they are blanks... if I have this in the rowdata, the non-blank data is output, otherwise it writes out the 'its blank!' =iif(Fields!IsraelCt.Value="", "its blank!", Fields!IsraelCt.Value) But this function returns the error: =Sum(IIF(Fields!IMyField.Value ="", 0, Fields!MyField.Value)) I would think that the function above would replace the value with a 0 when SUMming it, if the value is blank. But obviously this isn't happening. Any ideas?

Try this: =Sum(iif(IsNothing(Fields!MyField.Value),0,iif(IsNumeric(Fields!MyField.Value), Fields!MyField.Value, 0 ))) Good luck

Hi Isham, Even with that I still get the same error- I would assume that if the data that's getting input was nonnumeric, that it would output zero (the second iif), but it looks like this isn't the case. Could somehow an empty string be treated as numeric?? Actually, I tried a function, and the empty string "" is NOT treated as numeric. But, the function below still returns the error. I am baffled. =Sum(iif(Fields!IsraelCt.Value="",0, iif(IsNumeric(Fields!IsraelCt.Value), Fields!IsraelCt.Value, 0 )))

I think you need to handle this with custom code. Try something like this: Public Function ReturnNbr(Num asString) ASInt32 IF ISNOTHING(Num)Then ReturnNbr =0 ELSEIF IsNumeric(Num) THEN ReturnNbr = CInt(Num) ELSE ReturnNbr =0 END IFEnd Function Then call it from your expression, something like: =SUM(code.ReturnNbr(Fields!MyValue.Value))

Great, thanks Isham, this worked like a charm!! I got too focused on trying to do the sum with the expression that I wasn't thinking about other ways to solve the problem. Thanks again, Chris

The reason behind this is that instead of replacing the Blanks with Val(0) or CDbl(0), you are specifying a string "it's blank", therefore even though you are converting this string and attempting to recreate the logic in the SUM, you are never going to be able to SUM two different data types without code as in the answer. The code is simply converting your "it's blank" back to a Int(0) which is more easily accepted by your SUM. I am not sure if this approach is going to be as happy if decimals came into play in a big way and would have stuck to amending the row expression to return a Dbl and then doing the same for the SUM You should have had in your rows =iif(Fields!IsraelCt.Value="", Val(0), Val(Fields!IsraelCt.Value)) and in your SUM =SUM(iif(Fields!IsraelCt.Value="", Val(0), Val(Fields!IsraelCt.Value))) No code needed there then :)