SSRS Custom Total

Question

I've got a series of tables that are based on a dataset of Account Groups and Account Codes in SSRS.

I'm using some custom code to return summed values from two other datasets to compare side by side. I'm returning correct values for both the individual accounts, and account groups, but am not able to get a grand total for each table correctly. Each table is filtered to only show specific account groups, so the total of the dataset is not the total of each table. I'm sure this is simple but it is eluding me.

Custom code I'm using is:

EDIT - got the subtotal for first tablix but now this issue:

I had to tweak it a bit but made it work. Essentially had to create a separate SumLookup function for the subtotals and return my total to that. However, I need to "reset" the variable for each tablix in my report. Currently, it's just aggregating every subtotal rather than just the ones in the current tablix. I would have thought that adding a line like this would have worked but isn't, thoughts? Function GetTotal() return grandTotal grandTotal = 0 End Function

SOLVED CODE

Dim grandTotal as New Decimal()

Function SumLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()
suma = 0
ct = 0
For Each item As Object In items
suma += Convert.ToDecimal(item)
ct += 1
Next
If (ct = 0) Then return 0 else return suma 
End Function

Function SumLookups(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim sumas As Decimal = New Decimal()
Dim cts as Integer = New Integer()
sumas = 0
cts = 0
For Each item As Object In items
sumas += Convert.ToDecimal(item)
cts += 1
Next
grandTotal = grandTotal + sumas
If (cts = 0) Then return 0 else return sumas 
End Function

Function GetTotal()
     return grandTotal
End Function

Function GetTotalReset()
    grandTotal = nothing
End Function

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-08-03T22:51:53

You don't get the total because suma variable is reset in every call of the SumLookup() function. I don't know if you are doing calls to the function in every row of the table (even for those filtered out) which causes incorrect totals. So if that is not your case you can create a variable outside of the scope of your SumLookup function, then just return that variable from another custom function.

Dim grandTotal As Integer;

Function SumLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()
suma = 0
ct = 0
For Each item As Object In items
suma += Convert.ToDecimal(item)
ct += 1
Next
grandTotal = grandTotal + suma
If (ct = 0) Then return 0 else return suma 
End Function

Function GetTotal()
    return grandTotal
End Function

An additional alternative is filter the data from your dataset, what leaves only the data is needed to calculate the correct total.

Let me know if this helps.

SSRS Custom Total

1 Answers