Creating a Chart
The following sample program (ex8c.sqr) builds on the report that you created in the topic “Creating Cross-Tabular Reports.”. That sample program combined two reports in one program. The following sample program produces two charts that correspond to the two cross-tabular reports.
Here is the code; the lines that were changed or added are shown like this:
Program ex13a.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
declare-chart orders-stacked-bar
chart-size=(70,30)
title='Order Quantity'
legend-title='Month'
type=stacked-bar
end-declare ! orders-stacked-bar
end-setup
begin-program
do select_data
do print_array
print '-' (+2,1,70) fill
position (+1)
do print_array2new-page
let $done = 'YES' ! Don't need heading any more
do print_the_charts
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 5if not ($done = 'YES')
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-if
end-heading
begin-procedure print_the_charts
print-chart orders-stacked-bar (+2,1)
data-array=order_qty
data-array-row-count=12
data-array-column-count=4
data-array-column-labels=('Jan','Feb','Mar')
sub-title='By Product By Month'
new-page
print-chart orders-stacked-bar (+2,1)
data-array=order_qty2
data-array-row-count=3
data-array-column-count=4
data-array-column-labels=('Jan','Feb','Mar')
sub-title='By Price Category By Month'
end-procedure ! print_the_charts