TOTALS: Printing Customer Orders for Several Regions

TOTALS lists customer order details by region. It demonstrates invaluable techniques, such as:

• The use of start and stop values, allowing the user to specify the range of regions to be printed when running the report
• How to print a running count on each detail line
• How to print a block of statistics after each region category, including the total number of records, the total value of orders and the average value of orders

Table Details and Type

It is possible to produce the report more quickly by specifying the REGION table as the sequencing table, otherwise the report would require sorting on the CUSTOMER.REGION_CODE field. Furthermore, there are fewer records to test the start and stop range against if the REGION table is used as the fast pass table.

Report Heading

"Prints customer order details with statistics for all regions".

Field Processing

The main features of the field processing are:

• Fields 1 to 5 print customer order details in five columns of data for each region

• Field 2, RUNNING_COUNT, prints a running total at each detail line by using the formula:
  PREVIOUS $ + 1

• Field 4, ORDER_VALUE, has totalling turned on, but list on break turned off. The fact that the field is totalled is very important. It means that the totalled order value is accessible on break lines, later in the report, just by referring to the ORDER_VALUE again, as in:

  Field 11 ORDER_AVERAGE
  Formula: ORDER_HEADER.ORDER_VALUE/ORDER_COUNT

  which prints the average order value for the region, and:

  Field 13 ORDER_TOTAL
  Formula: ORDER_HEADER.ORDER_VALUE

  which prints the total order value

• Fields 5 to 13, are printed after the region break, have no headings, and are specified as list on break

• Field 5 is folded so that the calculated field 7, LABEL_STATISTICS, is printed beneath the listed fields for each region. To print some text that varies depending on the region, the formula for LABEL_STATISTICS is:

  "STATISTICS FOR " CONCAT REGION.NAME

  Only fields specified in the field list can be used in formulas, so REGION.NAME is declared as field 6

• Fields 8 and 9 print the subtotal line:

  "Total number of orders: " ORDER_COUNT

  where field 9, ORDER_COUNT, has the formula: 1, and is totalled to provide a count of the records printed in the region category

• Fields 10 and 11 print the subtotal line:

  "Average order value is: " ORDER_AVERAGE

  where field 11, ORDER_AVERAGE, has the formula ORDER_HEADER.ORDER_VALUE/ORDER_COUNT, and is not totalled (ORDER_VALUE was totalled in Field 4, ORDER_COUNT was totalled in Field 9)

• Fields 12 and 13 print the subtotal line:

  "Total value of orders is:" ORDER_TOTAL

  where Field 13, ORDER_TOTAL, has the formula: ORDER_VALUE, and is not totalled

Fields processed by program:

Nr Name             Type Occurs Sort Tot List L/Brk P/Brk Rpt Page Break Fold

1  REGION_CODE       U3                   Yes   No    No  No   Yes   No   No
2  RUNNING_COUNT     N3              No   Yes   No    No  No   No    No   No
3  NAME              C30                  Yes   No    No  No   No    No   No
   in CUSTOMER
4  ORDER_NUMBER      L5              No   Yes   No    No  No   No    No   No
5  ORDER_VALUE       N8.2            Yes  Yes   No    No  Yes  No    No   Yes
6  NAME              C30                  No    No    No  No   No    No   No
   in REGION
7  LABEL_STATISTICS  C45                  No    Yes   No  No   No    No   Yes
8  LABEL_COUNT       C25                  No    Yes   No  No   No    No   No
9  ORDER_COUNT       N5              Yes  No    Yes   No  No   No    No   Yes
10 LABEL_AVERAGE     C25                  No    Yes   No  No   No    No   No
11 ORDER_AVERAGE     N7.2            No   No    Yes   No  No   No    No   Yes
12 LABEL_TOTAL       C25                  No    Yes   No  No   No    No   No
13 ORDER_TOTAL       N10.2           No   No    Yes   No  No   No    No   No

Record Selection Condition

Records are selected from the fast pass table REGION, depending on the start and stop values entered at runtime.

Output Options

Notice that the report asks the user for start/stop values, that Pause after each page ? is 'Yes', and that the grand totals are suppressed.

Running the Report

REPPRINT -DB SALESORD -PROG TOTALS

Enter the start value, for example "CAM", press <Tab> and enter the stop value "SEE".

The start value must appear earlier in the record sequence than the stop value. For example, entering "SEE" followed by "CAM", produces an empty report.

Alternatively, the start and stop values can be preloaded with command tails like this:

REPPRINT -DB SALESORD -PROG TOTALS -START_1 CAM -STOP_1 SEE