Dynamic Grouping In Reports

Here’s a way to reuse the same report with dynamic Group By levels, which I touched on in my Jan-2001 article “Temporary Tables with No Bloat.” I’ll use a simple sales total example, where sales can be rolled up by country, by region, or by division:

  1. Create a report submission form (frptSubmission) with a single column combo box (cboGroupBy) to let the users select the level of detail they’d like from a value list (Country, Region, Division). Add a command button (cmdSales) with code to open the sales report (rptSales) on click.
  2. Create this public function:
Public Function rptSalesGroupBy() as String rptSalesGroup = _
    Forms![frptSubmission].cboGroupBy.Column(0) End Function

Using a function keeps your options open, in case you decide later to submit without being tied to the form (for example, from a scheduler, from another entry point in your application, and so forth). Add your own error handling, of course.

  1. Create a select query (qselSales) against the sales transaction table (tblSales) with joins to the other tables that a user might wish to roll up by (for instance, join tblSales.DivisionID record to tblDivision.DivisionID, tblDivision.RegionID to tblRegion, and tblRegion.CountryID to tblCountry.CountryID). Group the query and the sum of the SalesAmount and the field that you want to group by, using this code:
(IIf(rptSalesGroup()="Country", _
  [tblCountry].[CountryID], _
  IIf(rptSalesGroup()="Region", _

Call this field LogicalGroup.

  1. Finally, create a report (rptSales) with two text boxes in the detail section (txtLogicalGroup and txtSalesAmount) bound to your select query fields (qselSales.SumOfSalesAmount and qselSales.LogicalGroup, respectively).

Voilà! One report, three outputs, depending on the user’s choice in the combo box. The magic is in step 3: converting the text of the user’s instructions into the appropriate field and doing a Group By on it. My technique means less maintenance for you when things change or need reformatting, and makes it very simple to create new versions of the report. It also has the not unpleasant side effect of giving the user the impression that you’ve written a lot more reports than you actually have.


About Doug Den Hoed

Doug is VP of Product Development for PEPR Inc, a Director at Lumina Consulting Ltd, and is transforming the way Oil & Gas companies do business with another new venture he initiated. He still enjoys working with Access.
This entry was posted in Reports and Graphs. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.