Robert Thompson Robert Thompson - 1 month ago 7
Vb.net Question

Reporting Services calculating sub totals using custom code issue

I am using custom code to calculate sub totals. The sub totals are running totals going downward.

The below custom code is working great if I run the report for 1 period.

Custom Code

Public Shared Dim SortCodeTotal as Decimal

Public Shared Function Initialize()
SortCodeTotal = 0
End Function

Public Function AddTotal(ByVal b as Decimal) as Decimal
SortCodeTotal = SortCodeTotal + b
return b
End Function

Public Function DisplayTotal(ByVal b as Decimal) as Decimal
Dim ret as Decimal = 0
ret = SortCodeTotal + b
SortCodeTotal = ret
return ret
End Function


Detail Line Contain this expression

=Code.AddTotal(Sum(Fields!Activity_Amt.Value))


Summary Total contains this expression

=Code.DisplayTotal(0)


The report is using a matrix. The custom code does not correctly when running with more then 1 period. How to I change the custom code to work on all periods in a matrix.

The sub totals work like a running total.

Example

Detail 1 $50
Detail 1 $50
Sub Total **$100**

Detail 2 $40
Detail 2 $40
Sub Total **$180**

Detail 3 -$50
Detail 3 $50
Sub Total **$180**

Detail 4 $20
Detail 4 $50
Sub Total **$250**


The totaling is happening on the previous sub total + the next detail lines

Click this to see how the report looks

Click this to review the report

Answer

It seems your issue is related to the way SSRS process the matrix. Each entire row is evaluated before pass to the next one, so your cumulative total is being calculated horizontally while you need the running total vertically for each column.

The below custom function uses a Collection to store the total of each period for each row.

Public Shared dict As New Collection

Public Function AddTotal(ByVal value as Double, ByVal period As String) As Object

    Dim subtotal As Double

    If not dict.Contains(period) Then
        dict.Add(value, period)
        subtotal = dict.Item(period)  
        Return subtotal
    End If
    subtotal = dict.Item(period) + value
    dict.Remove(period)
    dict.Add(subtotal,period)
    Return dict.Item(period)

End Function

To call this function use:

=Code.AddTotal(sum(Fields!Activity_Amount.Value),Cstr(Fields!Period.Value))

I think there is no need for additional functions, since this returns the cumulative total in each evaluation as long as you use it in the correct scope, inside the period group, outside the details group and inside the Sort Code group.

enter image description here

It produces:

enter image description here

In my dataset Period field has the values 1 and 2