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:  1)  index the tables; 2) deal with the null values; 3) create calculated fields in the master rowset, and 4) build the report.

The technique described here assumes that the reader is already familiar with datamodule creation, master-detail rowset linking (see, for example, Chapter 6 of the VdB7 Users’ Guide), and fundamentals of working with the report designer (see, for example, the papers on this topic at Ken Mayer’s web site as well as his comprehensive tutorial).  If you’ve already done all that, then proceed as follows:

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

2. 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
                          // nulls for multi-table reports
  local i
  for i = 1 to r.fields.size
    do case
      case r.fields[i].type = "CHARACTER"
        r.fields[i].beforegetvalue := class::nullchar

      case r.fields[i].type = "DATETIME"
        r.fields[i].beforegetvalue := class::nulldate

      otherwise
        r.fields[i].beforegetvalue := class::nullvalue
    endcase
  endfor

function nullvalue
  if this.parent.parent.endofset==false
    return this.value
  endif
  return 0

function nullchar
  if this.parent.parent.endofset==false
    return this.value
  endif
  return ' '

function nulldate
  if this.parent.parent.endofset==false
    return this.value
  endif
  return ctod(' ')

function calcfield(j)   // calculated field function to keep from
                        // reinventing the wheel
  c = new field()
  c.fieldname := j
  c.length := 6        // length doesn’t appear to affect the actual
                       // value, but keeps these at a manageable width
  this.rowset.fields.add(c)
  return

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!

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

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

     A)  Only use those groups and field summaries that are either fields within the primary rowset, or added as calculated fields to that rowset.
     B)  Secondly, whatever grouping you choose, make sure that the indexes you have set in the datamodule are consistent with sort order of the desired groups — there is no autosort feature to save you here (and if, for some reason, it has been left on, turn it off immediately!).
     C)  When adding fields to group and/or detail bands, use tables and fields that are consistent with your datamodule design.  For example, if you have a Headquarters group, then an Office group, a Furniture Type Group, and a detail band, add fields from the Furniture table to the detail band, fields from the Office table to the Office group band, and fields from the Headquarters table to the Headquarters band.  Don’t just throw any field from any table anywhere in the report; although the designer will certainly let you do this, the results will most likely not be what you anticipated.
     D)  One report «wizard» you can use here is the «Add Groups and Summaries» menu option under «Layout» on the report designer menu.  This will constrain your group and summary choices to whatever’s in the master rowset automatically.  Be advised, however, that the «Summaries» feature treats the calculated fields as character, rather than numeric.  You can still use it, but you’re limited to functions like «count» and «max».  I’ll usually just select one of them, and then edit the text value after the fact, changing:

 {||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 …

     E)  When designing the report, you may want to change the calcdatamodule class to return something other than blanks — otherwise, where your data is missing values, you'll have nothing to visually grab hold of in the designer.  For the ‘function nullchar’, for example, change the “return ' '” to “return 'X'”.  Then you'll end up with “X” as a placeholder for blank values while designing the report.  When you're done,  just delete the “X” in the datamodule base class.

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!