Creating dynamic form to build reports from

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need advice: Is it possible to build a form that would allow a user to build
a completely dynamic query, which in turn would produce a report, based on
what the user chose in the form? As an example, choose any available field,
group by any/all of those fields, choose a summary/detail report type, use
any kind of aggregate on the data, etc. I know how to build the form, I just
can't see how the query would work or how I could build the report based on
the choices made.

Totally dynamic? Sort/filter/etc, what ever they want with the data then
produce a report?

Does anyone have an example of what this form may look like? The code behind
it to create the query and build the report? I know this is a lot to ask, but
I just can't see how it would be possible or where to start. I am proficient
in VB, but I don't think ACCESS reporting has the functionality to be so
flexible.
 
I been writing commercial business software for 20 years continues now.

In every application I have built (job costing, assessment management,
Invoicing, sales and quotation systems, Tour reservations systems..this is
list goes on an on), I have NEVER had to build a dynamic report.

You certainly can and should offer a nice bunch of options that let the user
EASILY select things like sort order, and certain options.

I mean, if the report is a sales report, then you need to allow the user to
select things like the sales region, the sales rep, and perhaps something
like what product. I mean, how many ways does the customer need to get a
sales report? There is really only 2 or 3 options needed for a given report.
I have applications that have been running for 17 years straight, and a
request for a new report might occur once in 6 to 8 years spans.

So, really, if you give a few options, then you are generally home free.

Here is a few more tips:
choose a summary/detail report type
Click to expand...

I OFTEN have the above option. So, for example you have lots of details, and
then "summary" stuff. The solution is to give the user a "check box" for
"summary data only". In the reports on-open event, you simply toggle (turn
off, or on) the reports "detail" section, and presttro...only the summary
data shows. (this works well, and even works with a mde since no design time
changes are needed).

And, take a look at the report prompt screens I have here:
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

If you look at those screens, you can see a "lot" of filter options in some
of the examples..but none of those reports modifies the structure of the
report at runtime. And, yes..I do even have some cross tab reports that fill
*some* text boxes at runtime. (In just a quick look, the amount of code
written for reports in one of my recent projects was 2200 lines of code. So,
sure, often a good bit of code can be used in a report...but I have NEVER
needed to change, or have design changes occur to those reports.

So, with good designs, and a bit of requirements gathering, you should be
able to give your client the reports they need, and once done...never hear
from them again....
 
The best example of what you describe is Access Report Design View. You seem
to want all, or almost all, the functionality of Access Reporting.

Every attempt that I have seen to implement "user reporting" in an
application cost a lot of time and effort and ended up in one of two
situations: (1) it was too complex and the users did not use it OR (2) the
capability was so simple that the users did not use it.

On the other hand, I have seen many situations where the users were given
relatively minimal instruction in creating reports, and a retail copy of
Access, and were successful in creating reports that were usable in their
work. The users were happy, the developers were happy, and the people paying
the bills were happy.

Thus, convincing the client to give the users full retail Access and
training them in creating reports seems a really good approach.

Larry Linson
Microsoft Access MVP
 
Back
Top