Last post Sep 06, 2012 07:45 AM by rizhaider
Jul 23, 2009 09:40 PM|EricCagle|LINK
I have a ReportViewer control displaying on an aspx page. The report contains a details section with a Table control. Within the Table I have a single grouping based on one field. The far right column of each detail row is a complex electrical calculation
based on several other colulumns on the same row (I have this column calculating correctly). In the Table footer, I want to display a total (or sum) of the calculated column. I have been looking for hours now and almost all of the answers say to use SQL
to calculate the answer at the time of the query. Because of the complexity of the calculated column, I would prefer to perform the calculations in the report.
I would assume there is a way to just write an expression something like "=Sum(Report!CalcField.Value)" but I cannot get anything to work.
Help on this would be greatly appreciated.
Jul 23, 2009 11:00 PM|FileFoundException|LINK
Give RunningValue a try; it may work depending on your computed column.
http://msdn.microsoft.com/en-us/library/ms159136.aspx for more.
Jul 27, 2009 03:52 PM|EricCagle|LINK
I do not believe that will work, or I do not know how to write the expression. In looking at the MS page, the expression used in the first parameter is "Fields!Fieldname.Value". In my case, I am not trying to sum a field from the dataset, but rather the
value of a calculation in another report textbox.
Jul 27, 2009 04:59 PM|FileFoundException|LINK
but rather the value of a calculation in another report textbox.
Is that field then coming from calculations done on one or more DataSet fields?
At any rate, it's really a lot better and much faster to solve the problem in SQL? Can you show what the expression looks like?
Jul 27, 2009 07:29 PM|EricCagle|LINK
Sorry, I should have mentioned this, but I don't believe that I can use SQL to calculate the sum because I am using grouping in the table on the report. I am performing the calculations as described but need to place the sum in a group footer row. I am
not sure that makes things easier or more difficult? Is there not a way to place the sum of a detail row of a table into the group footer of a table? This seems like it should be a basic function of the reportviewer tool.
Jul 27, 2009 10:32 PM|FileFoundException|LINK
You did not answer the question, though. This column the sum of which you want to put in the footer is based on calculations done on fields? Yes? If so you can put these calculations in the RunningValue expresssion parameter.
If you can, please provide a sample of your expression.
Jul 27, 2009 10:54 PM|EricCagle|LINK
OK, sorry for not being clear on your question/comment. I have changed the query slightly to simplify the calculation process.
In the table control on the report, I have a detail row with the following fields: kW Peak Capacity, Estimated Load Factor, and Annual kWh. The first 2 fields are now directly from the query (Fields!Capacity and Fields!EstLF). The third column is a calculated
value based on the previous two fields =((Fields!Capacity.Value)*(Fields!EstLF.Value)*8760). This part works fine.
In the group footer of the table control, I simply want to get a Sum of the third column (Annual Kwh).
I have tried =Sum((Fields!Capacity.Value)*(Fields!EstLF.Value)*8760) and your suggestion of RunningValue. Both just give a message on the report "#Error".
Jul 28, 2009 02:37 PM|FileFoundException|LINK
What is the error? If you're in Visual Studio, it will give a message in the Output window.
I actually tried both methods with your expression and they do give the sum without errors.
Jul 28, 2009 04:01 PM|EricCagle|LINK
Like I stated, the report runs with no problems but instead of showing the value of the Sum expression I get "#Error" on the report in place of the calculated value. I am getting this regardless of either function I use. I do not see where a description
of the error message is.
I am going to create another report and keep everything simple just to test the formula. If you could explain how you tested, I would appreciate it.
Jul 28, 2009 04:16 PM|FileFoundException|LINK
I just created very simple report with dummy data [two fields] and then I created table with a footer. in one cell of the footer row I added the sum expression and it worked as expected.
Usually when your report generates an error or in invalid operation, the report builder inside Visual Studio will show a warning in the output window or errors/warnings window. Do you see anything in that window?
Jul 28, 2009 04:38 PM|EricCagle|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
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?
Jul 28, 2009 04:56 PM|EricCagle|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!
Jul 28, 2009 06:24 PM|FileFoundException|LINK
That's why I like to use t-sql function IsNull.
Sep 06, 2012 07:45 AM|rizhaider|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.