Robert Thompson - 1 year ago 82
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

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
subtotal = dict.Item(period)
Return subtotal
End If
subtotal = dict.Item(period) + value
dict.Remove(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.

It produces:

In my dataset `Period` field has the values 1 and 2

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download