#error when summing numbers in report builder?

confusedKid picture confusedKid · May 10, 2012 · Viewed 14.3k times · Source

I'm trying to sum some numbers up in a grouping, and sometimes a #error would show up instead of an actual number. I understand that this may happen when dividing by 0 or when there are nulls in the dataset, but in the dataset, I only see valid numbers. The numbers aren't large enough to cause an overflow, and I'm certain the expression is correct, as the #error only shows up for a particular combination of parameters.

Is there another reason why I'm seeing the #error message?

To be clear, the formula is =Sum(Fields!Number.Value)

Edit: Any of the aggregation functions also result in this message.

Answer

Jamie F picture Jamie F · May 10, 2012

Sometimes SSRS decides that a number field returned from the dataset is text. Then many VB functions will fail.

See if converting the number to a decimal fixes things:

=SUM(CDEC(Fields!Number.Value))