Running Sum in Group Footer (2003 SP2)

Topic

New Reply

I am showing detail for a group that keeps a running sum on an amount field. I have calculated a % threshold in a field and when the running sum goes over that $ amount I have made the line not visible. However, I would like to show this last $ amount below the threshold in the group footer. This threshold changes as I go through the next member of the group but of course the running sum keeps accumulating the amounts even though the lines are not visible. Does anyone have any ideas how to capture the sum or just the amounts that are visible? Thanks in advance, Norma

Reply | Quote

Replies

Place an unbound text box txtTotal in the group footer.
Add code similar to the following to the On Format event of the detail section, subsituting the appropriate names:

If Me.txtRunningSum <= TresholdValue Then
Me.txtTotal = Me.txtRunningSum
End If

The value of txtTotal is set to that of the running sum as long as that hasn't passed the treshold.

Reply | Quote

That works for the first person in my group, but the txtTotal stays the same for the next person in the group and I need that to start fresh with the new threshold value. Now my report prints correctly for the first person but is blank for the remaining people in the group because the txtTotal remains the same. Is there a way to reset txtTotal = 0 as it makes its way through the group? Thank you! Norma

Reply | Quote

Did you chose to have the field accumulate as a running sum “over group,” or “over all?”

Reply | Quote

I set it to Over All because if I just do the Over Group it doesn’t calculate properly. – it just gives me the same total as the field. My report sort is by person, then clients (that’s the running total). Thank you, Norma

Reply | Quote

Well, I think if Han’s method works fine for the first person, it should work for the next — if you do use the running sum over group. I think (maybe I’m not figuring this out correctly) but I think you really must have a “running sum over group,” so it can restart at zero for each client — unless you want to do that all in code. If the “over group” isn’t working, you might want to re-check your sorting?

Ignore this that I wrote earlier , but just leaving it in FYI: How to you make the lines invisible — IOW, since you’ve probably got some code to make the line invisible, can you set a public variable at that time that (preventing it from incrementing further while with the current client is the same) and stores the amount you want to display in the group footer, and and then display it? and, upon the client changing, you set this public variable back to zero?

Pat

Reply | Quote

The method I described assumed that you wanted a running sum per group, so the Running Sum property should be set to Over Group. I don’t understand what you want to do if it is set to Over All. Could you provide more detailed information?

Reply | Quote

I actually have four group sorts so I made another running sum and changed the code appropriately. It now seems to be working. Thank you both so much!! Norma

Reply | Quote

Great! Glad it’s working.

Reply | Quote