#error when summing numbers in report builder?

Question

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.

Hexone

Answer 1

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

Dicker

This works! Thanks. Is this a documented bug in Report Builder? Edit: I spoke too soon it seems. It fixed the problem when using a specific set of parameters, but another parameter produces this problem again :( – Hexone

Are you sure that this field is really a number for all rows returned? – Dicker

Yes, in the query designer, they're all numbers. I've also replaced all the null values with 0's. – Hexone

This answer saved me! – Sukkah

You saved my ass , Thanks – Jinn

When after searching for an hour for a solution to a nonsensicial problem an answer suddenly works. Thanks a bunch! – Ommatophore