Saturday, November 12, 2011

SSRS - Aggregate of an Aggregate

So here is how to subtotal a column that is itself a sum function. SSRS 2005 wont allow you to aggregate an aggregate function. For example, the total of a column showing a Running Total, useful in daily stock balance calculations.
Add the following code to the report  Report > Properties

Dim public totalBalance As Decimal 
Public Function AddTotal(ByVal balance As Decimal) AS Decimal totalBalance = totalBalance + balance return balance 
End Function 
Public Function GetTotal() return totalBalance 
End Function

This code adds two variables: totalbalance and cnt as decimal numbers. And two functions AddTotal and GetTotal.
AddTotal allows items in rows to be added up, use as follows in a value cell, where you had;


in the total cell, where it you were unable to simply use

use instead

Simply add more variables and public functions if you need to sum the sum of more that one field.
Inspired by this post on averages of averages link