Solved

# Report grouping and subtotals

Posted on 2013-09-23
Medium Priority
437 Views
Dear experts -
I have ca. 16 complex totals that display in a group footer - these (I learned the hard way) are not calculated 'on-the-fly', but each is the sum of a field calculated already in the SQL record source.
NOW - I have a subgrouping, before we get to the major grouping. The totals appear JUST FINE in these subgroupings.
For example, let's say the major group is by person, and we have Joe, Mary, Katie.
Within each person, we have a job type: type 1, 2, 3. (It is a finite list of just 3).
I would like to display the subtotals for job TOGETHER, in the group footer.
Is there some way to do this that does not require me to create ANOTHER 48 FIELDS in the record source (!) - can I do something like have the subtotal on the report, but make it invisible and then display that value in the group footer?
0
Question by:terpsichore

LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39514632
<<Is there some way to do this that does not require me to create ANOTHER 48 FIELDS in the record source (!) - can I do something like have the subtotal on the report, but make it invisible and then display that value in the group footer?>>

Yes, you can do that, but you'd have to do this totalling on your own with code in the report.

In the reports module, you'd declare variables to hold the totals:

Dim curDollarTotal1 as Currency

Then in the appropriate header (ie. People) OnFormt event, set it to 0:

curDollarTotal1 = 0

Then in the detail's or group footer's OnFormat event, add to it:

If FormatCount = 1 then
curDollarTotal1 = curDollarTotal + <some control>
End If

and last, push the total into a control in the footer's OnFormat event:

Me![<some control>] = curDollarTotal1

Jim.
0

Author Closing Comment

ID: 39514693
perfectly stated, works beautifully. thank you.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.