[AccessD] report grouping on the fly?

Hi, Roz (and Tom),

I used the following approach to create a report grouped according to
user preferences.  Not near as complex as what you're proposing, but
this might be helpful as a starting point for where you're going.
Here's what I did:

1.  Built a query using generic aliases for the variable
grouping/sorting fields (EmployeeName as GroupSortVal1,  EmployeeNumber
as GroupSortVal2, etc.)

2.  Designed a report based on the query, using the generic field names
as the group/sort values.

3.  Designed a report spec form with combo boxe(s) offering a selection
of grouping options.

4.  User selects the desired group field(s), and I use the choice(s) to
build the SQL for a new query, assigning the chosen grouping fields to
the appropriate aliases.

5.  Update the query's SQL property with the new SQL string.

6.  Open the report.

Since the grouping/sorting is being done in the report on generic
aliases, the report doesn't care which fields/values from the query the
aliases represent.  Could get kinda ugly, but I'd guess you could
pre-position other controls bound to aliased fields, and assign fields
to the aliases as needed for other elements of the report, too.

HTH

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Roz Clarke
Sent: Tuesday, March 08, 2005 9:04 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] report grouping on the fly?


Dear all

My colleague Tom and I are working on a reporting tool. The principles
are
borrowed from Steve Nyberg's very lovely RFil. (Wih a quite astonishing
amount of fiddle to get 

We are finding that the demand for bespoke report 'templates' (i.e.
pre-created reports that they can then set criteria against) is
outstripping
our ability to provide them, because users constantly want different
sorting
and grouping. 

Has anyone ever built anything that created group headers & footers on
the
fly? I am trying to conceptualize it but it seems like a big task.

Thinking aloud here - I can see that we need to limit the options
presented
to the user. They will have little understanding of the data or the
principles of data interrogation. So we will need to determine which
grouping functions to make available and which report fields each
function
can apply to.

We will then need to create the appropriate report sections and populate
them with controls, nicely positioned, suitably formatted and containing
the
correct data binding / grouping function.

I think positioning them will turn out to be the hardest part; it's such
a
visual task when you put total controls on manually.

Any thoughts or advice would be welcome

Cheers

Roz