No Pocky for Kity (Custom Aggregates)

While SQL Server Reporting Services supports a variety of built-in aggregates, one of the things that people ask for is the ability to define custom aggregates. While this is not directly supported in RDL, there is a trick you can do using the <Code> block within the report.

For example, let's say your report contained a query that returned a non-unique set of OrderIDs with associated freight amounts. You need to do a sum of freight values for distinct values of OrderID. In the Code block (available from the Report Properties dialog), you would add:

Dim orderIDs As System.Collections.Hashtable
Dim total As Double

Function MyFunc(ByVal orderID As Object, ByVal freight As Obect) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
MyFunc = total
End If
End Function

In your report, you add a hidden textbox with the value expression to compute the value:

=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))

In the footer of the table, you add a textbox with the value expression:

=Code.MyFunc(Nothing, Fields!Freight.Value)

to return the total value.

Caution: This is a workaround that isn't guaranteed to work in releases after SQL Server 2005. I'm providing it in case there is no way you can get around it and you are willing to retest your reports with future versions of Reporting Services. Thanks to Fang Wang for the code sample.