Reporting with dBASE Plus

Reporting with dBASE Plus:
A Tutorial

Michael Nuwer
October 28, 2002

Session I
Introduction

Goals and Objectives

The goals for this Session are:

To introduce the reporting Tutorial;
To provide an overview of dUFLP reporting classes;
To install this Tutorial;

Introduction

To many dBASE users, myself included, the Report class is a formidable object. The reporting tools can often be confusing and frustrating, and yet, at the same time, they are wonderfully powerful. The purpose of this Tutorial is to explore the many dimensions of the dBASE report engine. Hopefully we can cut through some of the confusion and reduce the frustration level.

This Tutorial assumes that you have completed the dB2K/dBASE Plus Tutorial. Material covered in "Creating Reports" will not be reviewed here. It is also a good idea to have read "Beginning Reports" and "Tricks with Reports and Labels" by Ken Mayer.

In this Tutorial we will try to go beyond these documents with examples that show you how to aggregate calculated fields; how to generate conditional summaries; how-to hide unwanted report elements; how to add conditional page brakes; and how to write to a table from a report. Most of the example reports reviewed in this Tutorial are taken from messages posted by Marko Mihorko in the dBASE newsgroups.

In addition to exploring the dBASE reporting tool, this Tutorial will look at the ReportViewer class, the Preview.wfm form from the dBASE User Function Library Project (dUFLP), and how the user interface interacts with reports. Finally we will discuss ways to prepare data for the reporting engine. In particular we will explore the use of temporary tables and the TMPQuery.cc class written by Todd Kreuter.

Custom Reports and Controls

Throughout this Tutorial we are going to use the custom reports and custom report controls that are included in the dUFLP. All the example reports are subclassed from duflp.crp. This file contains a basic custom report that can be used as a template for your own reports. It is similar to the base.crp custom report used in the dB2K/dBASE Plus application Tutorial. In addition it contains code that integrates a subclassed report with the Preview.wfm form, which is also in the dUFLP.

Along with duflp.crp, the dUFLP contains duflplnd.crp and duflp2cl.crp. The former file contains a custom report with a standard landscape layout and the latter file contains a custom report with a two columns layout. If you need a report with a gutter, binding.cc has the code you need to produce this in your dBASE report.

The dUFLP also contains a set of report controls. These are found in RepCntl.cc. They are mainly text and shape objects that can be added to a report. The report examples in this Tutorial make use of the custom controls in this file. An explanation of these controls is discussed in the file.

Setting Up the Tutorial

ReportingTutorial.zip

I have tried to keep this Tutorial very simple to install. There are neither database aliases nor source code aliases. You should download the example files and unzip them to a folder on one of your hard drives. The root folder will contain a bunch of report files, some custom report and custom control files, and three dbf tables.

All of the reports in this Tutorial are based on data from these three tables. You can explore the tables and their attributes by opening the DataTables.dmd datamodule. Dogs contains data for some 7,000 animals, including their registration number (reg_no), their name and sex, the registration number of their dam and sire, and their date of birth. Litters contains data for about 2,700 litters and Dams contains data for about 350 female dogs.

I hope that this simple data set is easily understandable so we can focus on creating the reports and not on complexities of the data. Thanks to Paul White for sharing these tables.

Bug Alert

There is a bug in dB2K/dBASE that can cause some annoyances while working with this tutorial. For some reason, dB2K/dBASE looses track of methods that are used in the report files. You will see this when you open a report in the designer and get a message stating something like:

"error: class does not exit: MY_REPORT::ROWSET_CANGETROW"

The method that dB2K/dBASE claims does not exist, in fact does exist. This is why the bug is annoying. The solution is to add a "set procedure" line at the top of the class.

class my_Report of DUFLPCREPORT from "DUFLP.CRP"
   set procedure to program(1) additive
   set procedure to repcntl.cc additive
   set procedure to REPORT.CC additive

The second line in the above example opens the report as a procedure file, making its functions, classes, and methods available for execution. The function "program(1)" returns the name of the currently executing program. So this line is like "set procedure to self."

Anytime you run into the "class does not exit" error (and you know that the class does exist), open the report file in the Source Code Editor and add the above line.

Unfortunately there's more. After you add this line, the report designer will open the report without an error message. But when you save your updates, the designer rewrites the class constructor and will remove the "set procedure" line that you have added. So you will need to add the "set procedure" line more than once.

The bug can also occur at runtime, that is, when the report is run and rendered to a report-viewer. I have added a workaround in all the example reports so that you shouldn't encounter the problem here. I have added a modified "bootstrapping" code at the top of each report file. It looks something like this:

   local r
   r = new My_Report()
   set procedure to program(1) additive
   r.render()
   return

The good thing is that you don't need to keep re-adding these lines because the report designer does not rewrite them.

Hopeful, annoyances are less problematic when you know why they occur and how to work around them. We might also hope that this bug is squashed in the next release of dBASE Plus. (Neither dB2K nor dBASE SE is being updated, so do not expect this bug to be removed.)

Understanding the Event Model

Object oriented programming is, among other things, event driven. dBASE provides a series of events that the programmer can use to create event handlers (or user defined methods). Our code executes in response to events that fire within the dBASE reporting engine. It is, therefore, useful to understand the behavior of the event model in order to know where to place code to get the end effect that you desire.

canRender
All visual components on a report, like text fields, text labels and shapes, as well as the StreamFrame have the canRender event. The event fires just before the component is rendered and the return value determine whether the component is displayed. It fires every time the object is rendered which means that for a component in a detail band, it fires for every row in the rowset.

preRender
Report bands (detailBand, groupHeaderBand, groupFooterBand, etc.) do not have a canRender event. Instead, they fire the preRender event. Like the canRender event, this event fires before the band begins rendering. Unlike the canRender event, however, you cannot prevent the band from rendering in the preRender event handler.

onRender
All visual components, the StreamFrame and all the bands have the onRender event. The onRender event fires after the component is rendered and it is fired every time the object is rendered. onRender also fires for the Band objects. This occurs after all the objects in that band have been rendered. And for StreamFrame objects, after that StreamFrame has been rendered, on each page.

In order to get a better idea of the order in which these events fire, lets look an actual report. Open EventOrder1.rep in the Report Designer. This report has a rather standard layout. The pageTemplate1 object is derived from a custom report (much like we did in the dBASE application tutorial). It contains some text objects for the title (at the top) and the page number (at the bottom). These objects will print on each page of the report.

(note: there are two examples of creating a cover page for your reports in "Tricks with Reports and Labels." See the dBASE Knowledgebase.)

The StreamFrame1 object contains the detailBand (pink area), and a group headerBand (green area) and footerBand (blue area). Each of the three bands contains a custom textField object (for printing data) and a textHeading object (for printing labels).

I think it will be a useful exercise to record the order in which the render events fire for a report such as this. I have, therefore, added a large number of event handlers which will print to a text file as the event fires. The only thing you need to do is run the report. (If you receive a message box that asks whether you want to overwrite a text file, click "Yes.")

This report is designed to render two pages. You can close report.

In the Navigator, click the "Other" tab and then double click the file named: EventOrder1.txt. (If you don't see this file, click View (in the menu) and then Refresh.)

This is the record of the order in which the events in the report fired. At the top of the file, you can see that the onOpen event of the text field objects in each of bands fired first. Other objects were also opened but we did not record these. The point is that all the visual objects are opened before the report renders and they are opened only once for the report.

Next the StreamFrame1 canRender event fires. Then the report starts printing by group.

Scroll down to Line 196. This line should show that the streamFrame1 object's onRender event fired after the bands printed a full page of data. (Visually this is near the bottom of the page.) Then the PageTemplate1 object is rendered. Notice that this includes the objects at the top of the page as well as those at the bottom.

Finally the onPage event fires.

Line 203 indicated the beings of page two. The StreamFrame1 canRender event fires first; then the bands are rendered; then (at line 399) the streamFrame1 onRender fires, and the objects on the PageTemplate are rendered.

Understanding the order in which objects are rendered is an import part of using the reporting tools. But it is not enough. We also need to understand how the rowsets are related to these events. When the report engine renders object in the streamFrame, it is also looping through the data in the rowsets.

To get a better understanding of how the report engine loops through the data, we will consider the EventOrder2.rep report. This report is very much like the report we considered above. The data is selected from two tables. The master (or parent) rowset is Dams1, which contains female dogs that have had litters. The detail (or child) rowset is Dogs1. It contains all the dogs in our database. When a master-detail relationship is set between these two rowsets, we can find each of the Dams' offspring.

Our report is a simple listing of this master-detail relationship. The data is group by Dam and the details list the offspring.

When you run this report, it will create another text file (EventOrder2.txt). In this file we will see when visual objects are rendered and we will see when the two rowsets are navigated. Understanding this behavior will help you know where to place code to get the effect that you desire.

When you open EventOrder2.txt in the Source Code Editor, you will see that both the master and child rowsets are opened. The master rowset starts at record one and the child rowset is also at record one.

When the report begins to render, the group headerBand is rendered (and prints data from the master rowset, record one). Then the first detail band is rendered (printing data from the detail rowset). After the detail band renders, i.e. after the onRender event fires, the detail rowset navigates to the second record. The detail band prints this record, the detail rowset navigates to the next record (number 3), and it is printed.

After the third detail record prints, the rowset navigates to "end of set." Then dBASE does some skipping around, so that before the next group footerBand renders, the master rowset row pointer is at record 1 and the detail rowset row pointer is at record 3. The group footerBand is then rendered after which the master rowset navigates to record 2 and the detail rowset navigates to the first linked record.

The reporting engine is now ready to print the next group and the process of navigating the two rowsets is repeated.

Many of the reporting examples used in this tutorial rely on an understanding of the reporting engine's event model. We will use the preRender or canRender events to set values before printing, and the onRender events to reset values after printing. Moreover, we will be able to move a row pointer to some desired location, evaluate a field value, and move the row point back. We can do all this because we have a clearer understanding of the event model.

Go to next Session
Go back to the tutorial menu

The Legal Stuff: This document is part of the dBASE Reporting Tutorial created by Michael Nuwer. This material is copyright © 2002, by Michael Nuwer. dBASE, dBASE Plus, dBASE SE, and dB2K are copyrighted, trademarked, etc., by dBASE, Inc., the BDE (Borland Database Engine) and BDE Administrator are copyrighted, trademarked and all that by Borland, International. This document may not be posted elsewhere without the explicit permission of the author, who retains all rights to the document.