report system architecture 1

[daneharnett]

Hi,
I would like to know if anyone has found a stable & effecient architecture to store/filter/display reports that are required by the system...

I expect my system to end up with many reports (>100), each report would need to access 1 or more queries and each report could need a custom form so the user can filter the output...

Does anyone have any ideas?

Thanks in advance

 

[dhookom]

I create three tables, one user form, and several developer forms to manage reporting.
Starting with the user form (frmRptSlct) which has all of the controls I need to filter the reports such as txtStartDate, txtEndDate, cboEmployee,... These may be on separate tab pages.

In addition to the filtering controls, there is a list box of all my reports (row source is tblReports) a button to launch the reports, an option group for mode (preview or print), and a couple others.

tblReports (one record per report)
rptRptID
rptObjectName
rptTitle
rptAvailable
rptDescription

tblCriteria (one record per criteria control)
crtCrtID
crtCtrlName

tblReportCriteria (one record per report per criteria)
rcrCrtID link to tblCriteria
rcrRptID link to tblReports

I have a form and subforms to manage which criteria pertain to which report which users don't see.

On my frmRptSlct, as a user clicks a report in the list box, I run code to enable and disable controls based on the tables. When the user runs a report, I build a strWhere by looping through all the criteria on the form finding controls that are not disabled.

Most of my apps use multi-select list boxes to select multiple employees or departments or cities or whatever. I have a generic function that dynamically builds a "[FieldA] In (....)" clause.

Duane
MS Access MVP

 

[daneharnett]

dhookom,
That sounds great and I think I could 'borrow' and modify that architecture for my own use...

Next question...

So far I have only made 3 reports, 1 is basically a chart...
This report is based on a query which itself is based on another query that needs criteria specified... Ive done this by using parameters but i dont like them so i referenced controls on my own filter form...

Problem is now i need to use that same query for a similar report, but this new report uses a different filter form...
I dont want to have 50 queries that are the same cept for the criteria...

Hope someone can help

ps. heres a star

 

[dhookom]

I will often use DAO to change the SQL of a querydef. You have to make sure your users each have their own front end.

Duane
MS Access MVP