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 DoubleFunction 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.
Comments
- Anonymous
October 09, 2006
The comment has been removed - Anonymous
November 07, 2006
Just set the total=0 just below the function declaration, it will reset for each row. - Anonymous
January 17, 2007
I have used the method from above. It works.... almost.The only thing I want to add is an extra filter that the function only works when a field is equal to a number (example Unit_Price = 3.86). Unit Price is at the moment NOT a part of the code, but it is already a part of the dataset.I have "translated" the code to:Dim Item_no As System.Collections.HashtableDim total As DoubleFunction MyFunc(ByVal Item_No As Object, ByVal WarehouseEntryQuantity As Object) As Double If (Item_No Is Nothing) Then Item_No = New System.Collections.Hashtable End If If (Item_No Is Nothing) Then MyFunc = total Else If (Not Item_No.Contains(Item_No)) Then total = total + WarehouseEntryQuantity Item_No.Add(Item_No, WarehouseEntryQuantity) End If MyFunc = total End IfEnd FunctionWhere can I insert the extra filter?Thx for helping me out..... - Anonymous
February 22, 2007
This works fine in case I have to insert Totals in Table Footer. If I have groups and I need to insert totals in every group footer, how can i do that?How can I limit the scope of that function to calculate the sum within a group.Thanks - Anonymous
December 18, 2007
Esta entrada se volvió a publicar en Blog a las 02:14:10 p.m. 18/12/2007Distinct Total and Subtotals on ReportsThis is a simple alternative that includes the ability for get grand total and sub totals.It is important to know that the life cycle of the report processing first get Grand Total, and after Subtotal, and finally the Details.When the report is been processing for the grand total, the local Dictionary (in code section) is filled with all unique keys then you need another Dictionary to store de unique keys for subtotals under each grouping.The scope is auto-controled if your ID’s are unique; if it is not, then you can change the “oid” parameter and local dictionaries to string type key instead of integer, and put a compound unique ID (your scope + your ID) in callingsThe Code section (in report properties) Private _DistinctSubTotal As System.Collections.Generic.Dictionary(Of Integer, Decimal) Private ReadOnly Property DistinctSubTotal() As System.Collections.Generic.Dictionary(Of Integer, Decimal) Get If (_DistinctSubTotal Is Nothing) Then _DistinctSubTotal = New System.Collections.Generic.Dictionary(Of Integer, Decimal) End If Return _DistinctSubTotal End Get End Property Private _DistinctGrandTotal As System.Collections.Generic.Dictionary(Of Integer, Decimal) Private ReadOnly Property DistinctGrandTotal() As System.Collections.Generic.Dictionary(Of Integer, Decimal) Get If (_DistinctGrandTotal Is Nothing) Then _DistinctGrandTotal = New System.Collections.Generic.Dictionary(Of Integer, Decimal) End If Return _DistinctGrandTotal End Get End Property Public Function GetDistinctTotal(ByVal oid As Integer, ByVal amount As Decimal) As Decimal If (Not Me.DistinctGrandTotal.ContainsKey(oid)) Then Me.DistinctGrandTotal.Add(oid, amount) Return amount Else Return 0 End If End Function Public Function GetDistinctSubTotal(ByVal oid As Integer, ByVal amount As Decimal) As Decimal If (Not Me.DistinctSubTotal.ContainsKey(oid)) Then Me.DistinctSubTotal.Add(oid, amount) Return amount Else Return 0 End If End FunctionThe calling convention from Expression setion in report designFor subtotals:=Sum(Code.GetDistinctSubTotal(Fields![(integer)TheID].Value, Fields![(decimal)TheAmount].Value))For grand total:=Sum(Code.GetDistinctTotal(Fields![(integer)TheID].Value, Fields![(decimal)TheAmount].Value))Replace “[(integer)TheID]” and “[(decimal)TheAmount]” with your own fields.Thanks and greetings. - Anonymous
February 15, 2008
The comment has been removed - Anonymous
January 21, 2009
PingBack from http://www.keyongtech.com/2187841-custom-code - Anonymous
November 25, 2014
Hi Brian,Sorry for posting years later..I had the same problem how you have described in your solution..Though debtors had multiple rows of data based on some seq value i have to calculate for distinct debtor amount in my report..After adding your code my report is displaying correct result in the footer..Can you please suggest me solution to reset the values in your code..Because when i run report for the first time values are coming correctly..when go back to the first page from the last page..Now actual field is setting to the final total which is changing the whole report..Please help me..Thanks a ton...