Using Business Charts

This chapter provides an overview of business charts and discusses how to:

Click to jump to parent topicUnderstanding Business Charts

Business charts are useful tools for presenting summary data. SQR provides two commands for creating charts— DECLARE-CHART and PRINT-CHART—and a varied set of chart types, including:

You can configure many attributes of SQR charts by activating three-dimensional effects or setting titles and legends. SQR charts are also portable: you can move them from one hardware platform to another.

You can prepare a business chart by using data that is held in an array, just as you would for a cross-tabular report. If you have already written a cross-tabular report, you need to take just one additional step to create a chart using the data that is already collected in the array.

See Creating Cross-Tabular Reports.

Click to jump to parent topicCreating a Chart

The following sample program builds on the report that you created in the chapter “Creating Cross-Tabular Reports” (ex8c.sqr). That sample program combined the two reports in one program. The following sample program produces two charts corresponding to the two cross-tabs.

Here is the code, with the lines that were changed or added 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_array2
  ​new-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 5
  ​if 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

Click to jump to parent topicDefining a Chart

The two chart sections in the ex13a.sqr program are specified with the DECLARE-CHART command in the SETUP section and are named orders-stacked-bar. The width and height of the charts are specified in terms of character cells. The charts that are generated by this program are 70 characters wide, which is 7 inches on a default layout. The height of the charts is 30 lines, which translates to 5 inches at 6 lines per inch. These dimensions define a rectangle that contains the chart. The box that surrounds the chart is drawn by default, but you can disable it by using the qualifier BORDER=NO.

The title is centered at the top of the chart. The text that is generated by LEGEND-TITLE must fit in the small legend box preceding the categories, so keep this description short. Generally, charts look best when the text items are short. Here is the DECLARE-CHART command:

declare-chart orders-stacked-bar
    chart-size=(70,30)
    title='Order Quantity'
    legend-title='Month'
    type=stacked-bar
  end-declare ! orders-stacked-bar

The heading is printed on the first page only.

Click to jump to parent topicPrinting a Chart

The PRINT-CHART commands are based on the orders-stacked-bar chart that was declared in the preceding section.

  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'

The data source is specified by using the DATA-ARRAY option. The named array has a structure that is specified by the TYPE option. For a stacked-bar chart, the first field in the array gives the names of the categories for the bars. The rest of the fields are series of numbers. In this case, each series corresponds to a month.

The subtitle follows the title and can be used as a second line of the title. A legend labels the series. The DATA-ARRAY-COLUMN-LABELS argument passes these labels. The DATA-ARRAY-ROW-COUNT argument is the number of rows (bars) to chart and DATA-ARRAY-COLUMN-COUNT is the number of fields in the array that the chart uses. The array has four fields: the product (or price category) field and the series that specifies three months.

Click to jump to parent topicRunning the Program to Create a Graphical Report

When you create a graphical report, you must use a slightly different technique for running the program and viewing the output:

You can also use the -KEEP command-line flag to produce output in the SQR Portable File format (SPF) and print it by using SQR Print. You still must use the -PRINTER:xx flag when printing.

See Printing with SQR.

Click to jump to parent topicPassing Data to the Chart

To pass the data to the chart, use the first field for the descriptions of bars (or lines or areas), and then use one or more additional fields with series of numbers. This procedure is common to many chart types, including line, bar, stacked-bar, 100 percent bar, overlapped bar, histogram, area, stacked-area, and 100 percent area. You can omit the first field and SQR uses cardinal numbers (1, 2, 3, and so on) for the bars. Only text fields are used for these options.

For pie charts, only one series is allowed. Pie charts are also a special case because you can specify which segments to explode, or pull away, from the center of the pie. By using a third field in the array, you can have a series of Y and N values that indicate whether to explode the segment. If Y is the value in the first row of the array, the pie segment that corresponds to the first row is exploded. With pie charts, you cannot omit the first field with the descriptions. Pie charts cannot have more than 12 segments.

Pie charts display the numeric value next to each segment. The description is displayed in the legend. In addition, SQR displays the percentage next to the value. You can disable this feature by using the qualifier PIE-SEGMENT-PERCENT-DISPLAY=NO.

When data is passed to an xy scatter plot or a floating-bar chart, the series are paired. A pair in a floating-bar chart represents the base and height of the bars. A pair in an xy-scatter plot represents x and y coordinates. In an xy-scatter plot, the first field does not have descriptions. In a floating-bar chart, the first field may have descriptions for the bars. For both types, you can have one or more pairs of series.