Selecting which subtotals to display (2003)

Topic

New Reply

I have a report with three levels of subtotals: Product, Zone, and Terminal. Works fine. Requirement is to allow the user to omit the Zone and Terminal subtotals — collapsing their detail. One way to do it is to maintain four different reports — on/off for each of the Zone and Terminal subtotals. But that seems like an awkward solution.

Is there a way to programmatically omit the two levels of subtotals. I can see how to render the group Headers and Footers invisible, but doing so retains their subtotalling effect. What’s the best approach?

Thanks.

Reply | Quote

Replies

Hiding group headers and footers won’t disable the grouping – that is determined by the existence of a group header and/or footer, visible or not. Unless you want to switch to design view in code and adjust the design of the report, which would be awkward and a lot of work, I don’t think there is a way to do what you want. Creating 4 reports is probably easier.

Reply | Quote

I was kinda afraid of that. But I awoke in the middle of the night with the stupid problem on my mind, and a solution popped into my head. If the group is based on a constant instead of a field from the recordset, then it effectively disables the grouping. Pulling out my trusty copy of Access 2002 Developer’s Handbook, I found I could manipulate the ControlSource of the grouping during the report’s Open event. Came up with this code in a report that normally shows ALL detail:

‘ hide Terminal info
If Not (Forms![frmEstimateFutureInventories]![chkDetailProduct]) Then
Me.Section(5).Visible = False
Me.Section(6).Visible = False
Me.GroupLevel(0).ControlSource = “=’x'”
End If

‘ hide zone info
If Not (Forms![frmEstimateFutureInventories]![chkDetailZone]) Then
Me.Section(7).Visible = False
Me.Section(8).Visible = False
Me.GroupLevel(1).ControlSource = “=’x'”
End If

It seems to do the trick, and now I need only one report instead of four.

Reply | Quote

Ah yes, that’s a clever solution!

Reply | Quote