Reporting services, sum all rows of a column

reporting-services

I'm using reporting services 2005, I have a report with a table(table1) which displays data from an sql database.
A column on the table displays numbers. I want the total of all rows for that column, on a textbox(textbox3)

of another table(lets call it table2).

I tried placing this on a table2 textbox: =Sum(ReportItems!textbox1.Value)
texbox1 is the one from the table1. But when going to the Preview tab i get:

Error 2 [rsAggregateReportItemInBody] The Value expression for the textbox 'textbox3' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

How can I solve this?

Thanks.

Best Answer

You've got a couple of options.

The first is to use an expression similar to the following in the Textbox:

=Sum(Fields!value.Value, "Values")

Where Values is the name of the DataSet you want to aggregate.

Here you're creating an aggregate expression and specifying the scope where it executes, in this case a DataSet, i.e. aggregate all values in the DataSet.

As you can see from the error you're getting you can reference a report item with an aggregate, but if you're displaying the aggregate in the table header or footer you can reference that Textbox.

Here's a simple report showing both methods:

enter image description here

enter image description here

Hopefully you can adapt one method to your report.

Related Topic