Understanding Cross-Tabular ReportsThis chapter provides an overview of cross-tabular reports and discusses how to:
Use an array.
Create an array.
Group by category.
Use multiple arrays.
Understanding Cross-Tabular ReportsCross-tabular reports are matrix-like or spreadsheet-like reports. These reports are useful for presenting summary numeric data. Cross-tabular reports vary in format. The following example shows sales revenue summarized by product by sales channel:
Revenue by product by sales channel Product Direct Sales Resellers Mail Order Total ---------- ------------ --------- ----------- ------- A 2,100 1,209 0 3,309 B 120 311 519 950 C 2 0 924 926 ---------- ------------ --------- ----------- ------- Total 2,222 1,520 1,443 5,185
This report is based on many sales records. The three middle columns correspond to sales channel categories. Each row corresponds to a product. The records fall into nine groups: three products sold through three sales channels. Some groups have no sales (such as mail order for product A).
Each category can be a discrete value of some database column or a set of values. For example, Resellers can be domestic resellers plus international distributors.
A category can also represent a range, as demonstrated in this example:
Orders by Product by Order Size Product Category Less than 10 10 to 100 More than 100 Total ----------- ------------ --------- ------------- ------- Durable 200 120 0 320 Nondurable 122 311 924 1876 ----------- ------------ --------- ------------- ------- Total 322 431 1443 2196
In this example, the rows correspond to the categories durable or nondurable. The columns represent ranges of order size.
For each record that is selected, the program must determine the range to which it belongs and add 1 to the count for that category. The numbers in the cells are counts, but they could be sums, averages, or any other expression.
Of course, other types of cross-tabular reports exist. These reports become more complex when the number of columns is not predefined and when more columns exist than can fit across the page.
Using an Array
Often, the program must process all of the records before it can begin to print the data. During processing, the program must keep the data in a buffer where it can accumulate the numbers. This can be done in an SQR array.
An array is a unit of storage that contains rows and columns. An array is similar to a database table, but it exists only in memory.
The sample program specifies an array called order_qty to hold the sum of the quantity of orders in a given month. You could program this specific example without an array, but using one can be beneficial. Data that you retrieve once and store in an array can be presented in many ways without additional database queries. The data can even be presented in a chart.
The sample program also demonstrates an SQR feature called a three-dimensional array. This type of array has fields (columns) and rows, and it also has repeating fields (the third dimension). In the order_qty array, the first field is the product description. The second field is the order quantity of each month. The example includes three months; therefore, this field repeats three times.
SQR references arrays in expressions such as array_name.field(sub1[,sub2]). The first subscript, sub1, is the row number. The row count starts with zero. The second subscript, sub2, is specified when the field repeats. Repeating fields are also numbered starting with zero. The subscript can be a literal or an SQR numeric variable.
program ex8a.sqr #define max_products 100 begin-setup create-array name=order_qty size={max_products} field=product:char field=month_qty:number:3 end-setup begin-program do select_data do print_array end-program begin-procedure print_array let #entry_cnt = #i let #i = 0 while #i <= #entry_cnt let $product = order_qty.product(#i) let #jan = order_qty.month_qty(#i,0) let #feb = order_qty.month_qty(#i,1) let #mar = order_qty.month_qty(#i,2) let #prod_tot = #jan + #feb + #mar print $product (,1,30) print #jan (,32,9) edit 9,999,999 print #feb (,42,9) edit 9,999,999 print #mar (,52,9) edit 9,999,999 print #prod_tot (,62,9) edit 9,999,999 position (+1) let #jan_total = #jan_total + #jan let #feb_total = #feb_total + #feb let #mar_total = #mar_total + #mar let #i = #i + 1 end-while let #grand_total = #jan_total + #feb_total + #mar_total print 'Totals' (+2,1) print #jan_total (,32,9) edit 9,999,999 print #feb_total (,42,9) edit 9,999,999 print #mar_total (,52,9) edit 9,999,999 print #grand_total (,62,9) edit 9,999,999 end-procedure print_array begin-procedure select_data begin-select order_date ! The quantity for this order quantity ! the product for this order description if #i = 0 and order_qty.product(#i) = '' let order_qty.product(#i) = &description end-if if order_qty.product(#i) != &description let #i = #i + 1 if #i >= {max_products} display 'Error: There are more than {max_products} products' stop end-if let order_qty.product(#i) = &description end-if let #j = to_number(datetostr(&order_date,'MM')) - 1 if #j < 3 let order_qty.month_qty(#i,#j) = order_qty.month_qty(#i,#j) + &quantity end-if from orders a, ordlines b, products c where a.order_num = b.order_num and b.product_code = c.product_code order by description end-select end-procedure ! select_data begin-heading 4 print $current-date (1,1) print 'Order Quantity by Product by Month' (1,18) page-number (1,64) 'Page ' print 'Product' (3,1) print ' January' (,32) print ' February' (,42) print ' March' (,52) print ' Total' (,62) print '-' (4,1,70) Fill end-heading
The following output is the output for program ex8a.sqr.
11-JUN-04 Order Quantity by Product by Month Page 1 Product January February March Total --------------------------------------------------------------- Canisters 3 0 0 3 Curtain rods 2 8 18 28 Ginger snaps 1 10 0 11 Hanging plants 1 20 0 21 Hookup wire 16 15 0 31 Hop scotch kits 2 0 0 2 Modeling clay 5 0 0 5 New car 1 9 0 10 Thimble 7 20 0 27 Thingamajigs 17 0 120 137 Widgets 4 0 12 16 Wire rings 1 0 0 1 Totals 60 82 150 292
Creating an Array
You must define the size of an array when you create it. The sample program creates the order_qty array with a size of 100.
The #DEFINE MAX_PRODUCTS 100 command defines the max_products constant as a substitution variable. The sample program uses this constant to define the size of the array. Using #DEFINE is a good practice because it displays the limit at the top of the program source. Otherwise, it would be hidden in the code.
The SETUP section creates the array by using the CREATE-ARRAYcommand. All SQR arrays are created before the program begins running. Their size must be known at compile time. If you do not know exactly how many rows you have, you must overallocate and specify an upper bound. In the example, the array has 100 rows, even though the program uses only 12 rows to process the sample data.
The preceding program has two procedures: select_data and print_array. Select_data performs the database query, as its name suggests. While the database records are being processed, nothing prints, and the data accumulates in the array. When the processing is complete, the print_array procedure does two things: the procedure loops through the array and prints the data, and it also adds the month totals and prints them at the bottom.
The report summarizes the product order quantities for each month, which are the records ordered by the product description. The procedure then fills the array one product at a time. For each record that is selected, the procedure checks to see whether it is a new product; if it is, the array is incremented by row subscript #i. The procedure also adds the quantity to the corresponding entry in the array based on the month.
This program has one complication: how to obtain the month. Date manipulation can vary among databases, and to write truly portable code requires careful planning.
The key is the datetostr function in the following command:
let #j = to_number(datetostr(&order_date, 'MM')) - 1
This function converts the order_date column into a string. (The ‘MM’ edit mask specifies that only the month part be converted.) The resulting string is then converted to a number; if it is less than 3, it represents January, February, or March and is added to the array.
Grouping by Category
The following output is a cross-tabular report that groups the products by price range. This grouping cannot be done by using a SQL GROUP BY clause. Moreover, to process the records in order of price category, the program would have to sort the table by price. The sample program shows how to do it without sorting the data.
The sample program uses an SQR EVALUATE command to determine the price category and assign the array subscript #i to 0, 1, or 2. Then it adds the order quantity to the array cell that corresponds to the price category (row) and the month (column).
Program ex8b.sqr #define max_categories 3 begin-setup create-array name=order_qty size={max_categories} field=category:char field=month_qty:number:3 end-setup begin-program do select_data do print_array end-program begin-procedure print_array let #i = 0 while #i < {max_categories} let $category = order_qty.category(#i) let #jan = order_qty.month_qty(#i,0) let #feb = order_qty.month_qty(#i,1) let #mar = order_qty.month_qty(#i,2) let #category_tot = #jan + #feb + #mar print $category (,1,31) print #jan (,32,9) edit 9,999,999 print #feb (,42,9) edit 9,999,999 print #mar (,52,9) edit 9,999,999 print #category_tot (,62,9) edit 9,999,999 position (+1) let #jan_total = #jan_total + #jan let #feb_total = #feb_total + #feb let #mar_total = #mar_total + #mar let #i = #i + 1 end-while let #grand_total = #jan_total + #feb_total + #mar_total print 'Totals' (+2,1) print #jan_total (,32,9) edit 9,999,999 print #feb_total (,42,9) edit 9,999,999 print #mar_total (,52,9) edit 9,999,999 print #grand_total (,62,9) edit 9,999,999 end-procedure print_array begin-procedure select_data let order_qty.category(0) = '$0-$4.99' let order_qty.category(1) = '$5.00-$100.00' let order_qty.category(2) = 'Over $100' begin-select order_date ! the price / price category for the order c.price &price move &price to #price_num evaluate #price_num when < 5.0 let #i = 0 break when <= 100.0 let #i = 1 break when-other let #i = 2 break end-evaluate ! The quantity for this order quantity let #j = to_number(datetostr(&order_date,'MM')) - 1 if #j < 3 let order_qty.month_qty(#i,#j) = order_qty.month_qty(#i,#j) + &quantity end-if from orders a, ordlines b, products c where a.order_num = b.order_num and b.product_code = c.product_code end-select end-procedure ! select_databegin-heading 5 print $current-date (1,1) page-number (1,64) 'Page ' print 'Order Quantity by Product Price Category by Month' (2,11) print 'Product Price Category' (4,1) print ' January' (,32) print ' February' (,42) print ' March' (,52) print ' Total' (,62) print '-' (5,1,70) Fill end-heading
The following is the output for program ex8b.sqr.
11-JUN-04 Page 1 Order Quantity by Product Price Category by Month Product Price Category January February March Total ----------------------------------------------------------------- 0-4.99 28 45 12 85 5.00-100.00 25 28 138 191 Over 100 7 9 0 16 Totals 60 82 150 292
Using Multiple Arrays
Using SQR arrays to buffer the data offers several advantages. In the previous example, it eliminated the need to sort the data. Another advantage is that you can combine the two sample reports into one. With one pass on the data, you can fill the two arrays and then print the two parts of the report.
The following sample program performs the work that is done by the first two programs. The SETUP section specifies two arrays: one to summarize monthly orders by product, and another to summarize monthly orders by price range.
Program ex8c.sqr #define max_categories 3 #define max_products 100 begin-setup create-array name=order_qty size={max_products} field=product:char field=month_qty:number:3 create-array name=order_qty2 size={max_categories} field=category:char field=month_qty:number:3 end-setup begin-program do select_data do print_array print '-' (+2,1,70) fill position (+1) do print_array2 end-program begin-procedure print_array let #entry_cnt = #i let #i = 0 while #i <= #entry_cnt let $product = order_qty.product(#i) let #jan = order_qty.month_qty(#i,0) let #feb = order_qty.month_qty(#i,1) let #mar = order_qty.month_qty(#i,2) let #prod_tot = #jan + #feb + #mar print $product (,1,30) print #jan (,32,9) edit 9,999,999 print #feb (,42,9) edit 9,999,999 print #mar (,52,9) edit 9,999,999 print #prod_tot (,62,9) edit 9,999,999 position (+1) let #i = #i + 1 end-while end-procedure ! print_array begin-procedure print_array2 let #i = 0 while #i < {max_categories} let $category = order_qty2.category(#i) let #jan = order_qty2.month_qty(#i,0) let #feb = order_qty2.month_qty(#i,1) let #mar = order_qty2.month_qty(#i,2) let #category_tot = #jan + #feb + #mar print $category (,1,31) print #jan (,32,9) edit 9,999,999 print #feb (,42,9) edit 9,999,999 print #mar (,52,9) edit 9,999,999 print #category_tot (,62,9) edit 9,999,999 position (+1) let #jan_total = #jan_total + #jan let #feb_total = #feb_total + #feb let #mar_total = #mar_total + #mar let #i = #i + 1 end-while let #grand_total = #jan_total + #feb_total + #mar_total print 'Totals' (+2,1) print #jan_total (,32,9) edit 9,999,999 print #feb_total (,42,9) edit 9,999,999 print #mar_total (,52,9) edit 9,999,999 print #grand_total (,62,9) edit 9,999,999 end-procedure ! print_array2 begin-procedure select_data let order_qty2.category(0)='$0-$4.99' let order_qty2.category(1)='$5.00-$100.00' let order_qty2.category(2)='Over $100' begin-select order_date ! the price / price category for the order c.price &price move &price to #price_num evaluate #price_num when < 5.0 let #x = 0 break when <= 100.0 let #x = 1 break when-other let #x = 2 break end-evaluate ! The quantity for this order quantity let #j = to_number(datetostr(&order_date,'MM')) - 1 if #j < 3 let order_qty2.month_qty(#x,#j) = order_qty2.month_qty(#x,#j) + &quantity end-if ! the product for this order description if #i = 0 and order_qty.product(#i) = '' let order_qty.product(#i) = &description end-if if order_qty.product(#i) != &description let #i = #i + 1 if #i >= {max_products} display 'Error: There are more than {max_products} products' stop end-if let order_qty.product(#i) = &description end-if if #j < 3 let order_qty.month_qty(#i,#j) = order_qty.month_qty(#i,#j) + &quantity end-if from orders a, ordlines b, products c where a.order_num = b.order_num and b.product_code = c.product_code order by description end-select end-procedure ! select_data begin-heading 5 print $current-date (1,1) page-number (1,64) 'Page ' print 'Order Quantity by Product and Price Category by Month' (2,10) print 'Product / Price Category' (4,1) print ' January' (,32) print ' February' (,42) print ' March' (,52) print ' Total' (,62) print '-' (5,1,70) Fill end-heading
The following is the output for program ex8c.sqr.
11-JUN-04 Page 1 Order Quantity by Product and Price Category by Month Product / Price Category January February March Total ---------------------------------------------------------------------- Canisters 3 0 0 3 Curtain rods 2 8 18 28 Ginger snaps 1 10 0 11 Hanging plants 1 20 0 21 Hookup wire 16 15 0 31 Hop scotch kits 2 0 0 2 Modeling clay 5 0 0 5 New car 1 9 0 10 Thimble 7 20 0 27 Thingamajigs 17 0 120 137 Widgets 4 0 12 16 Wire rings 1 0 0 1 ---------------------------------------------------------------------- 0-4.99 28 45 12 85 5.00-100.00 25 28 138 191 Over 100 7 9 0 16 Totals 60 82 150 292
SQR arrays are also advantageous in programs that produce charts. With the data for the chart already in the array, presenting this cross-tabular report as a bar chart is easy.