#error when summing numbers in report builder?

reporting-services reportbuilder reportbuilder3.0
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))

Related questions

Carrying out a SUMIF like operation using SQL Server Report Builder

I'm trying to produce a conditional sum in SQL Server Report Builder 3.0. My expression looks like this: =Sum(Iif(Fields!ProjectTypeID.Value=2,Fields!kWp.Value,0)) I'd hoped that this expression would produce a sum of the kWp of all projects …

Read More
How do I make wildcards work in a like operator in SQL Server reporting services?

I have added a filter expression using the like operator. What do I put in the value field? my parameter is named @test and I'd like the filter to function as a like %@test% would in SQL. I want it …

Read More
Report Builder 3.0: How to convert string representation of dates in mm/dd/yy format using CDate

I'm building a report using Report Builder 3.0. The source system I'm working with has strings representing dates in the format of mm/dd/yy, but I want to show this to the end users as dd-MMM-yy instead. Using CDate on …

Read More