Create a sum of calculated rows in Microsoft Reporting Services

Question

It seems like it should be simple, but I still can not find anything. In Reporting Services, I have a table with 6 rows, each of which has calculated values ​​and dynamic visibility. I would like to summarize these lines. Basically, I have several elements of the account and you want to do a general thing. I can’t change anything on the DB side, as my stored procedures are used elsewhere in the system. Each row also extracts data from a different data set, so I cannot make the sum of the data set. Is it possible to summarize all rows with a table footer? Similar to summing multiple rows in Excel? It seems very redundant to output the visibility expression from each row to the footer row to calculate the sum.

Answer 1

A few ways to achieve this:

1. Perform calculations in SQL and summarize this field like this:

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

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

2. Create a second dataset that calculates the total for you and uses it in the footer instead of the sum:

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

3. Do this using custom code. Right-click on the layout area, select Properties and go to the Code tab. Enter 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

In the Details dialog box, make the column in which you sum the field this expression:

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

This will execute the code above and complete your calculation plus calculate the total amount in this process.

, :

=Code.TotalAmount

. , , , (, Sum Detail), , .

Answer 2

db .

, SQL ?

:  , SUM (OrderQty) OVER (PARTITION by SalesOrderID) AS 'Total'

: OrderQty - , SalerOrderID "GROUP BY"

COUNT, AVG ..

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

Answer 3

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