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.
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.
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.
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.
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".
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.
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?
EricCagle
Member
3 Points
16 Posts
How do I sum a calculated column in ReportViewer?
Jul 23, 2009 09:40 PM|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.
Table sum reportviewer
FileFoundExc...
Contributor
2633 Points
428 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 23, 2009 11:00 PM|LINK
Give RunningValue a try; it may work depending on your computed column.
=RunningValue (Your_Expression,Sum,Nothing)
http://msdn.microsoft.com/en-us/library/ms159136.aspx for more.
EricCagle
Member
3 Points
16 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 27, 2009 03:52 PM|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.
Any idea?
FileFoundExc...
Contributor
2633 Points
428 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 27, 2009 04:59 PM|LINK
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?
s
EricCagle
Member
3 Points
16 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 27, 2009 07:29 PM|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.
FileFoundExc...
Contributor
2633 Points
428 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 27, 2009 10:32 PM|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.
EricCagle
Member
3 Points
16 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 27, 2009 10:54 PM|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".
FileFoundExc...
Contributor
2633 Points
428 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 28, 2009 02:37 PM|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.
EricCagle
Member
3 Points
16 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 28, 2009 04:01 PM|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.
FileFoundExc...
Contributor
2633 Points
428 Posts
Re: How do I sum a calculated column in ReportViewer?
Jul 28, 2009 04:16 PM|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?