SSRS Group Variables SUM & VB.NET Custom Code

Question

I have an SSRS report built using group variables with expressions that do field calculations. The group varaibles are providing correct results between 3 datsets using lookups, arithmatic, and logic operations.

I need to SUM total the results of the variable textbox expressions in the tablix footer. But variables will not work in this way and when I try different expressions in the tablix footer I get errors.

So I did online search of summing group variables and I came to custom code solution using VB.NET variables with functions to aggregate then display the values. But the custom code is not quite working. Please see the custom code at the bottom of the page. And here are some issues I've observed

Custom Code issues

1. If I use variable as Public Dim the total values changes to 0 when exported to excel (e.g.- "1,214,284" on the screen; "0" when exported to excel.)
   • If I change declare the variables as Public Dim to Public Shared Dim then the values on screen are the same and they will export to excel.
   • The problem is Public Shared Dim seems to work great in Visual Studio. But when executed on the Report Server, the variable accumulates every time the report is executed (i.e., ExecEvent #1: "150 value" on the screen & excel; ExecEvent #2: "300 value‬" on the screen & excel; ExecEvent #3: "450 value‬" on the screen & excel).

Any help? How can I make these values aggregate and export? How to get the custom code VB variables to behave correctly. Particularly the variable initialization on the server getting correctly set and reset.

Custom Code correction notes

• In the "add" function I added return thisValue to fix an issue where the details variable values with blank (not printing)

References

SSRS Summing Group Variables outside of the group

SSRS code variable resetting on new page

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d4a3969a-f3fc-457b-8430-733d7b590ff6/use-reportitems-to-sum-all-textboxes-in-a-tablix?forum=sqlreportingservices&prof=required

---

TablixDesign-WithGroupVaraibles

• Group row, Group footer row

Tablix Footer: group variable expression errors outside of group

• NOTE: These expressions are not allowed

• Variables!varExpenditureLifetime.Value --ERROR: Expressions can only refer to a Variable declared within the same grouping scope, a containing grouping scope, or those declared on the report. Letters in the names of variabels must use the correct cast.

• Sum(Variables!varExpenditureLifetime.Value) --ERROR: Variables cannot be used in aggregate functions

REPORT CODE:

Group Variable ("varLWSBegin_LifetimeExpense")

= Code.addLWSBeginLifetimeExpense(CDbl(
IIF(Parameters!boolUseLwsBalance.Value = false, 0,
Lookup(Fields!ProjectId.Value, Fields!ProjectId.Value, Fields!LWSBegin_LifetimeExpense.Value, "dsCAPEXAmountsCustomDataUpload"))
))

Tablix group row

Variables!varLWSBegin_LifetimeExpense.Value

Tablix footer row

Code.getTotalLWSBeginLifetimeExpense()

VB.NET Custom Code

' 
' Add group variable values to an external variable declared on the VB Code. 
' In Tablix Group Properties variable the value will be the call to the addValue function 
' Then on your textbox you call the getValue function: 
' Group Var: Code.addValue(Fields!my_field). 
' Textbox:   Code.getValue() 
' 
' variable1, addTotalBudget, getTotalBudget
' variable2, addLWSBeginLifetimeExpense, getLWSBeginLifetimeExpense
' variable3, addExpenditureLifetime, getExpenditureLifetime
'

'TEMPLATE
Public totalMyField2 as Integer
Public totalMyFieldName as Integer

Public Function addMyFieldName(ByVal thisMyFieldName AS integer)
    totalMyFieldName = totalMyFieldName + thisMyFieldName
End Function

Public Function getMyFieldName()
    return totalMyFieldName
End Function

'MyVariables
Public Shared Dim totalTotalBudget as Integer
Public Shared Dim totalLWSBeginLifetimeExpense as Integer
Public Shared Dim totalExpenditureLifetime as Integer

Public Shared Function Initialize() 
    totalTotalBudget = 0
    totalLWSBeginLifetimeExpense = 0
    totalExpenditureLifetime = 0
End Function

'Functions
Public Function addTotalBudget(ByVal thisValue AS Integer )
    totalTotalBudget = totalTotalBudget + thisValue
    return thisValue
End Function

Public Function addLWSBeginLifetimeExpense(ByVal thisValue AS Integer )
    totalLWSBeginLifetimeExpense = totalLWSBeginLifetimeExpense + thisValue
    return thisValue
End Function

Public Function addExpenditureLifetime(ByVal thisValue AS Integer )
    totalExpenditureLifetime = totalExpenditureLifetime + thisValue
    return thisValue
End Function

Public Function getTotalBudget()
    return totalTotalBudget
'   Dim myval as Integer = totalTotalBudget
'   totalTotalBudget = 0
'   return myval
End Function

Public Function getTotalLWSBeginLifetimeExpense()
    return totalLWSBeginLifetimeExpense
'   Dim myval as Integer = totalLWSBeginLifetimeExpense
'   totalLWSBeginLifetimeExpense = 0
'   return myval
End Function

Public Function getTotalExpenditureLifetime()
    return totalExpenditureLifetime
'   Dim myval as Integer = totalExpenditureLifetime
'   totalExpenditureLifetime = 0
'   return myval
End Function

'<END>

Answer 1

Easiest approach to be able to use variables like you are trying to is to use Report Variables instead of Group Variables. If you use Report Variables, it can be accessed from any scope.

You might have to change your dataset a little bit so that the aggregation is unmistakably grouped when calculated at the report level. I could not really guide you with that because I don't know your data like you do. But as an easier route to the scope of your aggregation, check this SO question. You can use your table group as the scope of your aggregate functions.

I've had to do something along the lines of what you are trying to accomplish and Report Variables came to the rescue for me. Let me know if this would work in your scenario.