  |
||
| Using Temporary Tables to Aid Complex Reporting | ||
| Mark Gonzalez from LabWare | ||
| Certain difficult reports can be made much easier to define and maintain by using
so-called "temporary tables". This is when a routine (like LIMSBasic, in our case) goes
through all the source tables and records, and creates simpler records in a Report_Header
and Report_Details tables. These latter tables are the ones used to generate the actual
report. The records in these tables can either be deleted after the report is run, or
kept for longer term use (like re-running the report without regenerating data). I use this trick when the customer is not yet certain of how they want a complex report to look. For example, if I am working on a COA that combines data from 10 tables (sample, test, result, analysis, units, prod specs, etc.), then I first get them to give me a Word version. The then create a simple report that uses Report Header and Report Details records. I fill the two tables manually with data that reflects their desired output. It is almost always the case when the see the output that they then want to organise the info differently (for example, group results in some weird way). I usually have to add another field or two to the Details table (such as a grouping field) and make simple changes to the report. Once we finally agree the layout, grouping, etc of the report, then it is simply a case of writing LIMSBasic that reads the source tables and creates the EXACT SAME records in the Header and Details tables. If the customer yet again changes their mind (they usually do) then I have some basic ammunition. As you know, when using a tool like Crystal Reports, even an apparently simple change (like adding a field to a report) can have dramatic changes. By keeping the report itself simple, and putting all the "intelligence" in the LIMSBasic routine, the report creation and maintenance is greatly simplified. My motto is: "The trick to reporting is not actually reporting, but rather ensuring that all desired data is available in the database". |
||
| © GeoMetrick Enterprises 2007 | ||