No, there are no errors being reported in visual studio--just the #Error on the report.
I created a simple report also. Nothing on it except a table. On the table there are 4 columns (name, capacity, EstLF, AnnualkWh). On the detail row of the table, the first 3 columns are coming directly from fields in the dataset. The fourth column contains
the expression "=Fields!Capacity.Value * Fields!EstLF.Value * 8760" and works correctly by giving the value of the calculation.
My problem is that in the fourth column of the footer row I want a sum of the fourth column and it gives an error. My exact expression is "=Sum(Fields!Capacity.Value * Fields!EstLF.Value * 8760)" without the quotes of
course.
Have you tried this exact calculation and had good results? As a test, I tried changing the sum expression to "=Sum(Fields!Capacity.Value * 1)". This should just total up the values of the Capacity column since 1 * Capacity.Value
would just give the field's value, right? Well, in doing that, I still get the same error on the report. It seems to not allow the multiplication of values within a Sum expression. Have you been able to make this work?
I just found out the problem... After much disgust with the reportviewer, it turns out that the error was caused by some bad data in the database. I had one record that had a NULL value for one of the fields. Although the reportviewer did not show it as
a problem for the field, when performing a calcualtion on the null value it gave an error.
To catch this in the future, I used the expression "=Sum((IIf(Fields!Capacity.Value Is Nothing, CDbl(0),Fields!Capacity.Value))*(IIf(Fields!EstLF.Value Is Nothing, CDbl(0),Fields!EstLF.Value))*8760)"
This converts NULL values to 0 within the calculation.
Thanks for all your help!
Marked as answer by EricCagle on Jul 28, 2009 04:59 PM
Great thinking I have struggled alot in a similar kind a prob. Thanks by your idea Eric, it really helped me, resolving my problem. But any how there must be a detailed error from ReportViewer or VS. I think MS should have to spend more time on ReportViewer
to be able to compete it with CrystalReports, no doubt Crystal Reports guys are far more advance in their business.
EricCagle
Member
3 Points
16 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 28, 2009 04:38 PM|LINK
No, there are no errors being reported in visual studio--just the #Error on the report.
I created a simple report also. Nothing on it except a table. On the table there are 4 columns (name, capacity, EstLF, AnnualkWh). On the detail row of the table, the first 3 columns are coming directly from fields in the dataset. The fourth column contains the expression "=Fields!Capacity.Value * Fields!EstLF.Value * 8760" and works correctly by giving the value of the calculation.
My problem is that in the fourth column of the footer row I want a sum of the fourth column and it gives an error. My exact expression is "=Sum(Fields!Capacity.Value * Fields!EstLF.Value * 8760)" without the quotes of course.
Have you tried this exact calculation and had good results? As a test, I tried changing the sum expression to "=Sum(Fields!Capacity.Value * 1)". This should just total up the values of the Capacity column since 1 * Capacity.Value would just give the field's value, right? Well, in doing that, I still get the same error on the report. It seems to not allow the multiplication of values within a Sum expression. Have you been able to make this work?
EricCagle
Member
3 Points
16 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 28, 2009 04:56 PM|LINK
I just found out the problem... After much disgust with the reportviewer, it turns out that the error was caused by some bad data in the database. I had one record that had a NULL value for one of the fields. Although the reportviewer did not show it as a problem for the field, when performing a calcualtion on the null value it gave an error.
To catch this in the future, I used the expression "=Sum((IIf(Fields!Capacity.Value Is Nothing, CDbl(0),Fields!Capacity.Value))*(IIf(Fields!EstLF.Value Is Nothing, CDbl(0),Fields!EstLF.Value))*8760)"
This converts NULL values to 0 within the calculation.
Thanks for all your help!
FileFoundExc...
Contributor
2633 Points
428 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 28, 2009 06:24 PM|LINK
That's good.
That's why I like to use t-sql function IsNull.
rizhaider
Member
2 Points
1 Post
Re: How do I sum a calculated column in ReportViewer?
Sep 06, 2012 07:45 AM|LINK
Great thinking I have struggled alot in a similar kind a prob. Thanks by your idea Eric, it really helped me, resolving my problem. But any how there must be a detailed error from ReportViewer or VS. I think MS should have to spend more time on ReportViewer to be able to compete it with CrystalReports, no doubt Crystal Reports guys are far more advance in their business.