Create Sum of calculated rows in Microsoft Reporting Services

Question

This seems like it should be simple but I can't find anything yet. In Reporting Services I have a table with up to 6 rows that all have calculated values and dynamic visibility. I would like to sum these rows. Basically I have a number of invoice items and want to make a total. I can't change anything on the DB side since my stored procedures are used elsewhere in the system. Each row pulls data from a different dataset as well, so I can't do a sum of the dataset. Can I sum all the rows with a table footer? Similarly to totaling a number of rows in Excel? It seems very redundant to put my visibility expression from each row into my footer row to calculate the sum.

Answer 1

A few ways you could achieve this:

1. Do the calculation in the SQL and sum that field, like so:

SELECT Quantity, Amount, Quantity * Amount As TotalAmount FROM MyTable

Then just use the TotalAmount field in your Detail row and sum it in the footer.

2. Create a second Dataset that calculates the total for you and use that in your footer instead of a sum:

=Sum(Fields!TotalAmount.Value, "MyTotalingDataset")

3. Do it using custom code. Right-click on the Layout space choose Properties and click on the Code tab. Put in the following code:

Public Dim TotalAmount As Double = 0

Public Function CalculateRowTotal(ThisValue As Double, ThatValue As Double) As Double
    TotalAmount = TotalAmount + (ThisValue * ThatValue)
    Return ThisValue * ThatValue
End Function

On the Detail band, make the column where you sum the field have this expression:

=Code.CalculateRowTotal(Fields!Quantity.Value, Fields!Amount.Value)

This will execute the code above and do your calculation plus calculate the total sum in the process.

The Footer band displays the total sum so the column has the expression:

=Code.TotalAmount

And you're done. Just be careful because you aren't guaranteed the order in which your code will execute and for some reports it will execute the footer first (for example, if you use the Sum of the rows in the Detail band) which would make the total zero as the Detail band calculations haven't happened yet, but for the general case this should work.

Answer 2

You could change the db as follows.

Did you know you can get aggregated results in SQL without aggregating the data?

Just add an extra column to the dataset as follows: ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'

In the above sample: OrderQty is the value you wish to sum SalerOrderID is the equivalent of 'GROUP BY'

You can use the same technique with COUNT, AVG and so on

More information here http://msdn.microsoft.com/en-us/library/ms189461(SQL.90).aspx

Answer 3

Thanks Adolf...worked like a charm!

Answer 4

good article. Really helped me

Answer 5

In case you have a problem with the execution order, add a text box below of the table and display TotalAmount in this box.