Progress
Programming
Handbook
Producing Categorized Reports
Control break reports list information broken down into categories. For example, items in the sports database are assigned to catalog pages. Every item also has an on–hand value (how many items are in inventory) and a price. The
p-wrk1.p
procedure produces a report that lists the value of the inventory for each catalog page:
This procedure produces the following report.
The control break (BREAK BY cat–page) in this procedure logically separates the item table into catalog pages. However, because you do not know what all the catalog pages are, or even how many catalog pages there are, the procedure must make two passes through the table:
- The BREAK keyword causes the first pass through the table. Progress sorts the item table and writes a sorted list to a temporary file (called a sort file). Progress determines at what points in the list the cat–page value changes.
- The second table pass occurs when Progress goes through the sort file, using the ROWIDs stored in that file to read item records from the item table. The ROWID is the internal database identifier that Progress associates with every database record.
Figure 15–1 shows how the procedure processes the item table.
Figure 15–1: Processing a Table for a Categorized Report
![]()
The following procedure uses work tables to produce the same report:
The numbers in the margin of the procedure correspond to the following activities:
- The DEFINE WORK–TABLE statement defines a work table, cpage, that contains two fields, w–cat–page and w–inv–value. The w–cat–page field has the same definition as the cat–page field in the item table.
- For each of the records in the item table, the procedure checks whether there is a cpage record for the page to which the item belongs. If there is not, the procedure creates a cpage record and stores the cat–page value in the cpage record.
- The = (ASSIGNMENT) statement accumulates the value of the inventory for each catalog page by adding the inventory value of the current item to the existing inventory value for the page (w–inv–value).
- The FOR EACH block sorts the cpage table and displays the inventory value of each catalog page.
Figure 15–2 shows the table processing this procedure performs.
Figure 15–2: Using Work Tables to Produce a Categorized Report
![]()
The work table version of the procedure also makes a pass through the item table. But it does not need to first sort the entire item table since it can create new work table records for each new catalog page as it encounters that page. The procedure then makes a pass through the cpage work table, building a sort file for that table. (Note that ROWIDs for work tables are always negative.) Finally, the procedure makes a second pass through the cpage table, retrieving the cpage records in the appropriate order.
You wonder: “If the
p-wrk1.p
procedure makes two table passes and thep-wrk2.p
procedure makes three table passes, why is the work table procedure any better than the procedure that uses only database tables?” There are two reasons:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |