10.6 Generating Columnar Reports

This section describes a small-scale business application that produces reports with dollar amounts. While this application doesn't introduce any new material, it does emphasize the data processing and reporting capabilities of awk. (Surprisingly, some people do use awk to write small business applications.)

It is presumed that a script exists for data entry. The data-entry script has two jobs: the first is to enter the customer's name and mailing address for later use in building a mailing list; the second is to record the customer's order of any of seven items, the number of items ordered, and the price per item. The data collected for the mailing list and the customer order were written to separate files.

Here are two sample customer records from the customer order file:

Charlotte Webb 
P.O  N61331 97 Y 045 	Date: 03/14/97
#1 3  7.50
#2 3  7.50
#3 1  7.50
#4 1  7.50
#7 1  7.50 

Martin S. Rossi 
P.O  NONE 	Date: 03/14/97
#1 2  7.50
#2 5  6.75

Each order covers multiple lines, and a blank line separates one order from another. The first two lines supply the customer's name, purchase order number and the date of the order. Each subsequent line identifies an item by number, the number ordered, and the price of the item.

Let's write a simple program that multiplies the number of items by the price. The script can ignore the first two lines of each record. We only want to read the lines where an item is specified, as in the following example.

awk '/^#/ {
		amount = $2 * $3
		printf "%s %6.2f\n", $0, amount
		next
	 }
{ print }' $*

The main procedure only affects lines that match the pattern. It multiplies the second field by the third field, assigning the value to the variable amount . The printf conversion %f is used to print a floating-point number; "6.2" specifies a minimum field width of six and a precision of two. Precision is the number of digits to the right of the decimal point; the default for %f is six. We print the current record along with the value of the variable amount . If a line is printed within this procedure, the next line is read from standard input. Lines not matching the pattern are simply passed through. Let's look at how addem works:

$ addem orders


Charlotte Webb 
P.O  N61331 97 Y 045 	Date: 03/14/97
#1 3  7.50  22.50
#2 3  7.50  22.50
#3 1  7.50   7.50
#4 1  7.50   7.50
#7 1  7.50   7.50

Martin S. Rossi 
P.O  NONE 	Date: 03/14/97
#1 2  7.50  15.00
#2 5  6.75  33.75

This program did not need to access the customer record as a whole; it simply acted on the individual item lines. Now, let's design a program that reads multiline records and accumulates order information for display in a report. This report should display for each item the total number of copies and the total amount. We also want totals reflecting all copies ordered and the sum of all orders.

Our new script will begin by setting the field and record separators:

BEGIN { FS = "\n"; RS = "" }

Each record has a variable number of fields, depending upon how many items have been ordered. First, we check that the input record has at least three fields. Then a for loop is built to read all of the fields beginning with the third field.

NF >= 3 {
for (i = 3; i <= NF; ++i) {

In database terms, each field has a value and each value can be further broken up as subvalues. That is, if the value of a field in a multiline record is a single line, subvalues are the words that are on that line. We can use the split() function to divide a field into subvalues.

The following part of the script splits each field into subvalues. $i will supply the value of the current field that will be divided into elements of the array order :

sv = split($i, order, " ")
if (sv == 3) {
       procedure

} else
       print "Incomplete Record"
} # end for loop

The number of elements returned by the function is saved in a variable sv . This allows us to test that there are three subvalues. If there are not, the else statement is executed, printing the error message to the screen.

Next we assign each individual element of the array to a specific variable. This is mainly to make it easier to remember what each element represents:

title = order[1] 
copies = order[2] 
price = order[3]

Then we perform a group of arithmetic operations on these values:

amount = copies * price  
total_vol += copies
total_amt += amount
vol[title] += copies
amt[title] += amount

We accumulate these values until the last input record is read. The END procedure prints the report.

Here's the complete program:

$ cat addemup


#! /bin/sh
# addemup -- total customer orders 
awk 'BEGIN { FS = "\n"; RS = "" }
NF >= 3 {
	for (i = 3; i <= NF; ++i) { 	
		sv = split($i, order, " ")
		if (sv == 3) {
			title = order[1] 
			copies = order[2] 
			price = order[3]
			amount = copies * price  
			total_vol += copies
			total_amt += amount
			vol[title] += copies
			amt[title] += amount
		} else
			print "Incomplete Record"
	}
}
END { 
   printf "%5s\t%10s\t%6s\n\n", "TITLE", "COPIES SOLD", "TOTAL"
   for (title in vol)
       printf "%5s\t%10d\t$%7.2f\n", title, vol[title], amt[title] 
   printf "%s\n", "-------------"
   printf "\t%s%4d\t$%7.2f\n", "Total ", total_vol, total_amt
}' $*

We have defined two arrays that have the same subscript. We only need to have one for loop to read both arrays.

addemup , an order report generator, produces the following output:

$ addemup orders


TITLE     COPIES SOLD      TOTAL

   #1              5     $  37.50
   #2              8     $  56.25
   #3              1     $   7.50
   #4              1     $   7.50
   #7              1     $   7.50
-------------
     Total        16     $ 116.25