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:

p-wrk1.p
FOR EACH item BREAK BY cat-page: 
   ACCUMULATE price * on-hand (SUB-TOTAL BY cat-page). 
   IF LAST-OF(cat-page) 
   THEN DISPLAY cat-page ACCUM SUB-TOTAL BY cat-page (price * on-hand). 
END. 

This procedure produces the following report.

  Cat-Page          TOTAL 
----------     ---------- 
        0     21,992.16
        1      8,489.92 
        3        450.00 
        4     12,377.40 
        5     10,575.00 
        6      1,445.52 
        7      1,615.27 
        8      1,367.10 
       11      7,339.40 
       12      2,250.00 
       13        854.00 
       14      2,074.83 
       15        587.50 
       16        256.86 
       17        325.71 

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:

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:

p-wrk2.p
/* 1 */
DEFINE WORK-TABLE cpage
       FIELD w-cat-page LIKE item.cat-page
       FIELD w-inv-value AS DECIMAL FORMAT "->>>,>>>,>>9.99"
           LABEL "Inventory Value".

/* 2 */
FOR EACH item:
    FIND FIRST cpage WHERE cpage.w-cat-page = item.cat-page NO-ERROR.
    IF NOT AVAILABLE cpage
    THEN DO:
        CREATE cpage.
        cpage.w-cat-page = item.cat-page.
    END.
/* 3 */
    cpage.w-inv-value = cpage.w-inv-value + (item.price * item.on-hand).
END.

/* 4 */
FOR EACH cpage BY w-cat-page:
    DISPLAY w-cat-page w-inv-value.
END. 

The numbers in the margin of the procedure correspond to the following activities:

  1. 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.
  2. 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.
  3. 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).
  4. 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 the p-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