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

 

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