Using the SQL Designer in VdB7, this is relatively easy to do — join the tables together on common fields, order it according to how you want to group the data on the report, and then build the report. For small to medium tables, this is certainly the quicker and easier thing to do. If your fictitious corporation has, say, 70 to 80 thousand pieces of furniture in their database, however, running the report may take a considerable amount of time, as the SQL generator needs to process the entire query prior to rendering the report. Even more painful, while designing it you’ll have to wait for all of that data to regenerate after practically every change in the Report designer.
In this paper we’ll discuss a solution to this dilemma: use a datamodule to link your rowsets together using master-detail relationships, and then build your report off of the datamodule. Using a datamodule, the Report designer is able to pull just enough information to render only the page currently being rendered, not the entire query, so the lag time experienced with the SQL-style join literally vanishes. Unfortunately, using multi-table datamodules in reports is not quite as straightforward as using a single SQL query, and most people have concluded that it is simply not possible. Not only is it possible, but the added benefits of having all of the rowset methods and properties available to you for each of your separate tables during the report generation and rendering process, in addition to improved performance, make this a desirable goal.
The roadmap to multi-table datamodule report happiness, therefore, is as follows:
Index the tables
Index all of the tables to be joined in like order according to how you plan to group the data in the report. For example, if our report is going to be grouped by corporate headquarters, then by regional offices, then by furniture, they all need to be indexed in that order. Say, for example, that the furniture table has both corporate headquarter and regional office fields; the index for that table should be on headquarters+office+furniture. The office table would be indexed on headquarters+office, and the headquarters table would be indexed by headquarters.
Get rid of those nulls
Nothing makes a multi-table datamodule
report more ornery than running
into null values. When rendering the report, the text values of the
report are expecting something; some offices may not have a certain piece
of furniture, and when the datamodule feeds this back to the report writer,
the usual response is «error: rowset at endofset», or words
to that effect. It is not a pleasant experience. I use the
following code in a custom datamodule base class to ward off these nulls
before they ever get to the report:
Class calcdataModule of DATAMODULE custom Function nullcheck(r)
//
use to return something other than
case r.fields[i].type
= "DATETIME"
otherwise
Function nullvalue
Function nullchar
Function nulldate
Function calcfield(j)
//
calculated field function to keep from
endclass |
|
Assuming that you have already
built a datamodule from this class with the headquarters, office, and furniture
tables, and set the master-detail relations with the appropriate indices,
open it in the source editor. To clear nulls from the furniture1
rowset, for example, you would go to the line which reads
with (this.furniture1.rowset) and,
between the with and
endwith statements for
the rowset, add the following two lines:
r = this.furniture1.rowset class::nullcheck(r) |
|
To play it safe, you probably want to add these statements to each of the rowsets in your datamodule — never know where those nulls will be coming from next!
Calculate some fields
You may have noticed that the final
function in our
calcdatamodule class has
nothing to do with checking for nulls — this is simply a shortcut for calculating
fields. Basically, you will have to add calculated fields to whichever
rowset is going to be the primary rowset for your report. What kind
of calculated fields? Any fields that you want to group by or perform
any kind of aggregate calculation on (e.g., summaries). Say, for
example, that the headquarters rowset will be your primary rowset, but
you want to include groupings by regional office and furniture type, as
well as subtotals of furniture. Calculated fields referencing these
child table data elements must be included in the query object’s
onOpen event:
class::calcfield(«Office») class::calcfield(«Furniture Type») class::calcfield(«Furniture Qty») |
|
After recompiling the datamodule
so that the calculated fields are available in the headquarters’ rowset
fields array, you can edit the corresponding calculated fields’
beforegetvalues directly
in the Datamodule designer to contain the following expression codeblocks:
this.parent.parent.parent.parent.office1.rowset.fields[«Office»].value this.parent.parent.parent.parent.furniture1.rowset.fields[«Furniture Type»].value this.parent.parent.parent.parent.furniture1.rowset.fields[«Furniture Qty»].value |
|
Recompile, save, and you may want to inspect the datamodule in the Designer one more time to make sure you’ve got the correct values in the correct calculated fields. And then we’re ready for:
Build the report
Of course, you need to go into the Designer, because the wizard won’t let you use datamodules. Drag your datamodule onto the blank design screen and have at it. Numerous tips and tricks concerning the report writer have been well represented by other authors in other articles in other places, and should be more than adequate to guide you in fleshing out the details of your report. Caveats to be added here with our datamodule report:
Only use those groups and field summaries that are either fields within the primary rowset, or added as calculated fields to that rowset.
{||this.parent.parent.agMax({||this.parent.STREAMSOURCE1.rowset.fields["Furniture Qty"].value})} |
|
to
{||this.parent.parent.agSum({||this.parent.STREAMSOURCE1.rowset.fields["Furniture Qty"].value})} |
|
and then it works fine. Assuming
that «Furniture Qty» really is a number, that is …
Finally, some notes on troubleshooting. Check and recheck your indexes. One of my most frequently recurring problems is that somehow one of my key indexes will get dropped (or I’ll forget to regenerate it when creating a new dataset), and neither the datamodule nor the report will tell me that it’s missing. Instead, you simply get incorrect reports, and wonder why. Also related to indexes, make sure (again) that the indexes in use logically track with the desired sort orders, groups, and summaries. In many ways, this is the most difficult part of the whole process. Finally, beware of the visual design editors — if you haven’t noticed yet, they will change your code, and not always for the better. A general rule of thumb would be to use the visual Designers to get the report and/or datamodule two thirds complete — after that, only use them to inspect your objects and check on available properties and methods, making all final changes in the source editor.
Well, that about wraps it up. Hopefully now, you, too, can begin developing rigorous, flexible, properly indexed, null-free, challenging multiple table datamodule reports!