SSRS code variable resetting on new page

Question

In SSRS 2008 I am trying to maintain a SUM of SUMs on a group using custom Code. The reason is that I have a table of data, grouped and returning SUMs of the data. I have a filter on the group to remove lines where group sums are zero. Everything works except I'm running into problems with the group totals - it should be summing the visible group totals but is instead summing the entire dataset. There's tons of articles about how to work around this, usually using custom code. I've made custom functions and variables to maintain a counter:

Public Dim GroupMedTotal as Integer
Public Dim GrandMedTotal as Integer

Public Function CalcMedTotal(ThisValue as Integer) as Integer
    GroupMedTotal = GroupMedTotal + ThisValue
    GrandMedTotal = GrandMedTotal + ThisValue
    Return ThisValue
End Function

Public Function ReturnMedSubtotal() as Integer
    Dim ThisValue as Integer = GroupMedTotal
    GroupMedTotal = 0
    Return ThisValue
End Function

Basically CalcMedTotal is fed a SUM of a group, and maintains a running total of that sum. Then in the group total line I output ReturnMedSubtotal which is supposed to give me the accumulated total and reset it for the next group. This actually works great, EXCEPT - it is resetting the GroupMedTotal value on each page break. I don't have page breaks explicitly set, it's just the natural break in the SSRS viewer. And if I export the results to Excel everything works and looks correctly.

If I output Code.GroupMedTotal on each group row, I see it count correctly, and then if a group spans multiple pages on the next page GroupMedTotal is reset and begins counting from zero again.

Any help in what's going on or how to work around this? Thanks!

I have the same issue. Good ol' Microsoft at it again. – John Strickler Sep 30 '10 at 15:43 — John Strickler, Sep 30 '10 at 15:43

score 20 · Accepted Answer · answered Sep 30 '10 at 15:56

20

Finally found the solution myself. Here it is, add Shared to the variable declarations:

Public Shared Dim GroupMedTotal as Integer
Public Shared Dim GrandMedTotal as Integer

answered Sep 30 '10 at 15:56

John Strickler

25,151
4
52
68

3

This should be marked as a solution because it works. For some reason referenced dlls are reset even when shared/static variables and methods are used on each new page. However, inline code blocks are not. So if nothing else, you can setup shared variables to save the totals page by page and let all of the other functionality stay in your dlls. – Frank Pearson Sep 16 '11 at 21:35
1

This answer is not correct. Try to run the report, then export to PDF. You'll see that GrandMedTotal is actually doubled. – Scott R. Frost Jun 04 '13 at 12:45
This is wrong. Shared variable will increase with every call. – husnain_sys Jan 22 '15 at 08:29
I try to turn between pages several times, and the value of Shared variable got double up each time. – Newbie Aug 20 '18 at 03:01

Scott R. Frost · Answer 2 · 2013-05-21T20:02:59.130

5

Just changing the variables to shared won't work. If you set them to shared they'll be DOUBLED when you export to PDF / XLS / etc (because it just kept adding to the existing var). You have to do something like this:

Public Shared Dim grandTotal as Decimal
Public Shared Dim costCenterTotal as Decimal
Public Shared Dim workerTotal as Decimal

Public Shared Function Initialize() 
    grandTotal = 0
    costCenterTotal = 0
    workerTotal = 0
End Function

Public Function AddTotal(ByVal b AS Decimal) AS Decimal
    grandTotal = grandTotal + b
    costCenterTotal = costCenterTotal + b
    workerTotal = workerTotal  + b
    return b
End Function

Public Function GetWorkerTotal()
    Dim ret as Decimal = workerTotal
    workerTotal = 0
    return ret
End Function 

Public Function GetCostCenterTotal()
    Dim ret as Decimal = costCenterTotal 
    costCenterTotal = 0
    return ret
End Function 

Public Function GetGrandTotal()
    Dim ret as Decimal = grandTotal
    grandTotal= 0
    return ret
End Function

edited May 21 '13 at 20:02

answered May 21 '13 at 19:57

Scott R. Frost

2,026
1
22
25

This one worked for me. Just using Shared causes the doubling issue discussed above. Note the use of Shared on the Initialize function so that it only gets called once. – John J Smith Feb 21 '16 at 22:24
3

How and when do you call Initialize() ?? – Jack Casas Feb 10 '17 at 12:05
I called initialize in the GetGrandTotal() function, that did it for me – asmgx May 12 '17 at 03:47
How to initialize? Where, When, How? Please advise! – SherlockSpreadsheets Dec 22 '19 at 15:13

score -2 · Answer 3 · answered May 09 '10 at 04:46

I don't know where do you use this. but in your case, if I were you, I just use simple expression to check visibility of SUM

for example I'd use Right Click On Sum Box \ Select Expression \ then use IIF(SUM <> 0, sum. "")

It worked on every where and wont reset, in your case you have a Region and your code will reset in every region so you willface with serios isses if you don't change your way.

SSRS code variable resetting on new page

3 Answers3

Linked