Master Index for reports, where to begin? 1

[debbrew]

Project to collect information about reports within our company and maintain what kind of report and other information as well as a revision number. Any ideas how to begin or get an example database to get a jump start?

 

[MichaelRed]

Oh, WOW. HUGE.

First, understand that this is a full time job for even a moderately sized company. Some few questions / thoughts:

Are all of the dbs "visible" on a network? Any that are kept on un-shared media are NOT going to show up.

You need a program which 'obtains' the network names of all resources on the network and their properties (read only/ read-write/not available).

A next module would need to retrieve the names of all of the dbs. Actually, you can probably just get teh "*.MDB" lists - but rember the routine needs to be recursive to ge each network resource and 'drill down' through all layes of folders (directories).

Having 'found' all the applicable mdbs. you need to itterate through the documents/reports collection of each, getting each of the reports collection objects.

Next is the process of opening each report and determining the recordsource of the report.

Now starts the hard part.

First, you need to define what is meant by " ... kind of report and other information ... "

But let me just assume that you will be building more or less a "data dictionary" for the reports. In its simple form, all you need to do is collect the field names used on the report from the recordsource and set up a correspondance table between the db, the report and the field name. Unfortunatly you need to resolve calculated and alais fields from queries back to basic tables. It does get worse - if you are really going to persue this, you need to also determine if a table referenced in the recordsource is the taret of a make table (e.g. any temp type object), and if it is - trace through to determine how it was constructed and continue to walk the information chain to the 'real source'.

NOW, it gets REALLY hard. You need to 'characterize" the report for human consumption. That is understand what the report is 'providing' to the users who use/access it. I do not know of any programatic process for this part, so you will need to find the users of each report and ask them what the report provides for them.

Next is the impossible. As soon asyou start takling to users (report consumers) you will become the target of N ^ N ( N ~= te number of users) requeste for changes to the reports.

In one of my jobs, I did something like this for a single database within the company. It had ~ 200 registered users and over 100 reports. Since I DID 'own' this db, I could accomodate their change requests. The only good news is that I was able to reduce the number od reports to ~ 20 and provide additional functionality. Many orf the existing reports provided the same info to different departmants or groups, just using specific identification criteria instead of parameterized criteria. By consoildating the parameters on a single form and providing access to the reports through the form for parameterization, a large (redundant) part of the database was simply un-necesary.

Just as an afer thought. This effort required several weeks on just the 'single' db.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[debbrew]

sorry for the skeleton information. basically, i need to help to approach this database from a standpoint that the user will enter the data manually and report on the results in grouping order of the type of report (excel, mainframe, access, COGNOS, etc.).

Any ideas to send me to an example for storing a list of reports that a user inputs new/changes/deletes manually through some kind of form associated to table(s).

thank you for your quick response and help.