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;


=RunningTotal(Fields!ColumnName.Value,sum,nothing) 
with
=Code.AddTotal(RunningTotal(Fields!ColumnName.Value,sum,nothing))

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


=sum(RunningTotal(Fields!ColumnName.Value,sum,nothing))
use instead
=Code.GetTotal()


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