Multiple Reports

[Dumferling]

I am working on a database where we are going to have to have a great many reports - at least a few hundred. I can see a looming issue as more reports are developed as to how to handle the criteria selection and generate the report - most reports will have different criteria and will look different. I am wondering if there are any ways to handle this other than a Report Index table which allows users to select the report and then opens a form where they can select the criteria. Is there any reading I can do to see how multiple reports can be handled better?

 

[theDBguy]

Hi. Not sure where I can point you to read anything about designing something like that, but I can tell you what I have done in the past.

What I have is a form with a Combobox to list all the reports. The combobox is based on a table listing all the reports and all their criteria requirements. For example, if a report can be filtered using a date, or a text (for last names or company names), or a Yes/No value.

When the user selects a report to print from the Combobox, certain controls on the form becomes visible, based on the expected criteria for that report. Then a button to view the report is used to open it using the criteria information entered by the user on the report.

Hope that helps a bit...

 

[Ranman256]

What could be simpler than user picks a report from the list, then clicks print?

along with the tReports table, I sometimes have to make a tRptBatch table, that
has serveral reports listed in a batch and users can print the batch:

Batch, Rpt
-----------------
Tax, rIRS1040ez
tax, rState740ez
Acctg, rTPSreport
Acctg, rQuarterly

 

[Dumferling]

Hi. Not sure where I can point you to read anything about designing something like that, but I can tell you what I have done in the past.

What I have is a form with a Combobox to list all the reports. The combobox is based on a table listing all the reports and all their criteria requirements. For example, if a report can be filtered using a date, or a text (for last names or company names), or a Yes/No value.

When the user selects a report to print from the Combobox, certain controls on the form becomes visible, based on the expected criteria for that report. Then a button to view the report is used to open it using the criteria information entered by the user on the report.

Hope that helps a bit...

This gives me some good ideas. I sort of was working along some of these lines but I can see from this that I can make it better. Thanks

 

[Dumferling]

What could be simpler than user picks a report from the list, then clicks print?

along with the tReports table, I sometimes have to make a tRptBatch table, that
has serveral reports listed in a batch and users can print the batch:

Batch, Rpt
-----------------
Tax, rIRS1040ez
tax, rState740ez
Acctg, rTPSreport
Acctg, rQuarterly

The problem is that most reports need filters at the time of viewing so printing is easy but having to select criteria makes it a bit more complex. I am looking at partially using Allen Browne's Search Criteria coding, combined with the idea of listing the criteria that are needed to produce a report and the idea from the DBGuy to make only some criteria visible. Some work to do here....

 

[Uncle Gizmo]

how to handle the criteria selection and generate the report

it's quite common to have a report with exactly the same layout, but using different criteria.

For instance you might want a report every month to show sales data or something similar.

The report would be exactly the same, except for the criteria changing, and delivering a different set of records. The display of the dates in the report heading would also be different.

I demonstrate one method of producing such a flexible report on my website here:-

Pass Info In to a Report - Nifty Access

Passing Information to a Report Often, I see examples where people pass information between Forms and Reports using Opening Arguments "openArgs" ... I did this myself until I happened upon an article, I think it was from "Smart Access" which explained a very satisfying way of Passing information...

www.niftyaccess.com

if you want a copy of the download file, you are welcome to a free copy. Contact me and I will explain how to get a copy for free.

 

[Uncle Gizmo]

In this demonstration (which was in answer to a question on access world forums, that's here) I use basically the same code as in my post above.

The main difference is the report needs to show the same layout, but with the criteria provided by a record set loop.

The reports are in a PDF format and then sent to each individual customer with code which you can find on Gina Whipps website, code for sending multiple emails. The link to Gina is in the Blog.

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

 

[Uncle Gizmo]

And here's a YouTube video where I answered another question on access world forums of a similar vein.

 

[Uncle Gizmo]

Regarding the other aspect of your question where you have many different reports, with many different criteria.

My approach which I believe I found on smart Access years ago would also work but you'd have to create the system.

Basically each report would have a boilerplate piece of code (exactly the same code) in the report code module.

Now each individual reports layout would be different, and require a different record source. However it is likely that each record source would require identical data, like start date, end date, customer name. In other words variables which can be provided to order.

Although the report data layout is unique, each report contains repeating elements, in that each report will require a heading, possibly a date range, an author, typical generic information.

Although you have technically 100 individual, different reports you also have 100 reports with some very similar requirements and these could be handled with the generic module code for each report.

And again, you have 100 different record sources. You could choose the report from a combobox which would store the report name along with the SQL for creating the reports record source.

Have a pop-up form where you would select the name of the report. This would also provide the sql. The Pop-Up form would provide the variables and introduce them into the report SQL statement and then the report SQL statement and the other boilerplate elements would be passed into the report with the custom property methods already demonstrated.

 

[Pat Hartman]

Here's a picture of a report picker in one of my apps. When you select the report, the variables for THAT report show. The ones with the underlined lables (Job in this example) are required. The others are optional. If you want more details, post back and I'll show what the table looks like.

 

[Isaac]

For reports where either 1) users have many different criteria, but report layout/columns are the same, OR, 2) users have many different criteria AND may want varying columns returned BUT they are agreeable with a simple Excel output:

I would put some time into creating a DIY report builder. I've created some of these with some satisfaction.
You can:

1. Allow them to select a column
2. Populate a listbox with all Distinct values in that column
3. Allow them to multiselect from the listbox and either Exclude or Include (sql NOT IN or IN)
4. Build up dynamic SQL, and output the result to Excel
5. Allow users to save that built-up SQL, giving their report a Name, and thus, being able to repeat and regenerate their own custom reports - making them either Publicly or Privately visible.

If you can get this to the point where it actually works, users LOVE it...absolutely love it.