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.

3. 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", _
  [tblRegion].[Region],[tblDivision].[Division]))

Call this field LogicalGroup.

4. 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.