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.


Comments (8)

  1. vodeth says:

    Hello Brian,

       This function No Pocky for Kity (Custom Aggregates) works for only one text box value. If I want to use the same funstion for another field value, then it returns the same value as it generated for the first one.

      I have the same scenario which have to calculate the values for distinct number.

      Can you please explain.

  2. Tony says:

    Just set the total=0 just below the function declaration, it will reset for each row.

  3. Cactus77 says:

    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.Hashtable

    Dim total As Double

    Function 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 If

    End Function


    Where can I insert the extra filter?

    Thx for helping me out…..

  4. disha says:

    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

  5. Asereware says:

    Esta entrada se volvió a publicar en Blog a las 02:14:10 p.m. 18/12/2007

    Distinct Total and Subtotals on Reports

    This 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 callings

    The 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 Function

    The calling convention from Expression setion in report design

    For 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.

  6. kitty2000 says:

    All,

    Your information above was very helpful.

    I had a matrix that I was trying to do a unique sum of a rowgroup for a subtotal. I used a modified technique of the first posting. The trick is to use the hashtable to store your unique values and then go through the hash table and add your values together to return from the function. See below code:

    Dim ClaimType1s As System.Collections.Hashtable

    Dim total1 As Double

    Function fGroupSum(ByVal TaxID1 As Object, ByVal ClaimType1 As Object, ByVal ClaimCt1 As Object) As Double

           If (ClaimType1s Is Nothing) Then

              ClaimType1s = New System.Collections.Hashtable

           End If

           If (ClaimType1 Is Nothing) Then

    Dim totalSum as double
    
    for each de As System.Collections.DictionaryEntry In ClaimType1s 
    
        if left(de.Key,instr(de.Key,&quot;_&quot;)-1) = TaxID1 Then
    
            totalSum = totalSum + de.Value
    
        End If
    
    Next de
    

              fGroupSum= totalSum

           Else

               If (Not ClaimType1s.Contains(TaxID1 & "_" & ClaimType1)) Then

                   total1 = total1 + ClaimCt1

                  ClaimType1s.Add(TaxID1 & "_" & ClaimType1 , ClaimCt1)

               Else

    total1 = 0
    

               End If

               fGroupSum= total1

           End If

    End Function

    Add a text box with the visibility hidden property set to true and add the following expression:  

    =Sum(Code.fGroupSum(Fields!TIN.Value, Fields!CLAIM_TYPE.Value,Fields!DSTHS_CLM_CT.Value))

    In the matrix: for the cell that needs the unique sum in the subtotal you use the InScope function and call the custom aggregate function as follows:

    =iif(NOT InScope("matrix1_ColumnGroup2"),"",

    iif(InScope(&quot;rowgroup_SrcFileNm&quot;),&quot;&quot;,
    
        iif(InScope(&quot;rowgroup_TIN&quot;),code.fGroupSum(Fields!TIN.Value, Nothing,Fields!DSTHS_CLM_CT.Value),
    
            iif(InScope(&quot;rowgroup_DATE_RCVD&quot;),Code.MyFunc(Fields!TIN.Value, Nothing, Fields!DSTHS_CLM_CT.Value),&quot;&quot;))))
    

    Cheers everyone! Thanks for the help!

  7. sam says:

    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…

Skip to main content