Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting which subtotals to display (2003)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting which subtotals to display (2003)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting which subtotals to display (2003)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting which subtotals to display (2003)

    Ah yes, that's a clever solution! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •