ReportViewer SUM Aggregate function shows #ERROR

Question

Hello,

I'm writing a report using Visual Studio 2005. I am displaying a field in a Table Body Column in a ReportViewer that is the total sum of ExpenseAmount , which is a float type in the database. I only want to sum of ExpenseAmount if the field 'new_datebilledtocustomer' is not null.

For this I wrote a calculated aggregate function in the data field, called Total_Owed .

=iif( isNothing(Fields!new_datebilledtocustomer.Value), 0, (Fields!new_expenseamount.Value))

So far the aggregate function works well; it prints the expenseamount if the field datebilledtocustomer is not null.

However the challenge here is to sum all of those expenseamount values. For some reason I keep getting #ERROR when trying to sum this field.When the Report shows the Table Column shows perfectly but the SUM function in the table displays #Error.

=SUM(Fields!Total_Owed.Value)
=CDbl(SUM(Fields!Total_Owed.Value))
both of the above functions give me #ERROR

Can anyone please help me in this one? Thanks very much in advanced for your help!

 

-Elizabeth K.

Answer 1

HI Elizabeth,

It seems to be Fields!new_expenseamount.Value is of string type, i suggest you to convert that to decimal or integer (CDbl() Or  CInt() Or Val()) befor you calculate Sum, so the change follows like this

=iif( isNothing(Fields!new_datebilledtocustomer.Value), 0, Val(Fields!new_expenseamount.Value)))
Or
=iif( isNothing(Fields!new_datebilledtocustomer.Value), 0, CDbl(Fields!new_expenseamount.Value))
And
=SUM(CDbl(Fields!Total_Owed.Value))

Hope this will help you

-Raja Sekhar K

Answer 2

Raja Sekhar, thank you for your reply!

Unfortunately it's still not working.. I changed the Fields!new_expenseamount.Value and the Sum to both of your suggestion, but I'm still getting #ERROR result... Any idea what might be happening here?

I'm  pretty sure the Fields!new_expenseamount.Value is float; it is set in our system as a 'Money' type field.

Thanks a lot for your help!

-Elizabeth

Answer 3

Actually i faced those problem many times, but i used the same type of expression it worked fine, I will try to find solution for this.

-Raja Sekhar K

Answer 4

Finally I found the solution to this problem.

Instead of writing:
=SUM(CDbl(Fields!Total_Owed.Value)) ;
I write:
=CDbl(SUM(Fields!Total_Owed.Value)) and in my case this one is working

Hopefully this helps anybody encountering this problem.