Controlling Page Breaks and Calculating Subtotals and Totals

When you use break logic, you may want to enhance your report by controlling page breaks or calculating subtotals and totals for the ON-BREAK column. The following example illustrates these techniques.

The sample program selects the customer's name, address, and telephone number from the database. The break processing is performed on the state column:

Program ex5d.sqr
begin-program
  do list_customers
end-program
begin-heading 4
  print 'Customers Listed by State' (1) center
  print $current-date    (1,1) Edit 'DD-Mon-YYYY'
  print 'State' (3,1)
  print 'Customer Name, Address and Phone Number' (,11)
  print '-' (4,1,9) fill
  print '-' (4,11,40) fill
end-heading
begin-footing 2
  ! print "Page n of m"
  page-number (1,1) 'Page '
  last-page   () ' of '
end-footing
begin-procedure state_tot  
  print '   Total Customers for State:  ' (+1,1)
  print #state_total () edit 999,999
  position (+3,1)     ! Leave 2 blank lines.
  let #cust_total = #cust_total + #state_total
  let #state_total = 0
end-procedure ! state_tot  
begin-procedure list_customers
let #state_total = 0
let #cust_total = 0
begin-select
! The 'state' field will only be printed when it
! changes. The procedure 'state_tot' will also be
! executed only when the value of 'state' changes.
state       (,1)  on-break print=change/top-page after=state_tot
name        (,11)
addr1       (+1,11)   ! continue on second line
addr2       (+1,11)   ! continue on third line
city        (+1,11)   ! continue on fourth line
phone          (,+2) edit (xxx)bxxx-xxxx ! Edit for easy reading.
  ! Skip 1 line between listings. 
  ! Since each listing takes 4 lines, we specify 'need=4' to
  ! prevent a customer's data from being broken across two pages.
  next-listing skiplines=1 need=4   
  let #state_total = #state_total + 1
from customers
order by state, name
end-select
if #cust_total > 0
  print '   Total Customers: ' (+3,1)
  print #cust_total () edit 999,999  ! Total customers printed.
else
  print 'No customers.' (1,1)
end-if
end-procedure ! list_customers

The output for the ex5d.sqr program is:

29-Apr-2004              

Customers Listed by State

State     Customer Name, Address and Phone Number
--------- ----------------------------------------
IN        Harold Alexander Fink
          32077 Cedar Street
          West End
          Davenport  [phone]
          Harry's Landmark Diner
          17043 Silverfish Road
          South Park
          Miningville  [phone]   
Total Customers for State:        2
MI        Sam Johnson
          37 Cleaver Street
          Sandy Acres
          Bell Harbor  [phone]   
	Total Customers for State:        1

NH        Jerry's Junkyard Specialties
          Crazy Lakes Cottages
          Rural Delivery #27
          Frogline  [phone]   
Total Customers for State:        1

...

Take a close look at the code. The data is printed by using a select paragraph in the list_customer procedure. The state and the customer name are printed on the first line. The customer's address and phone number are printed on the next three lines.

The program also uses the argument AFTER=STATE_TOT. This argument calls the state_tot procedure after each change in the value of state.

See Setting Break Procedures with BEFORE and AFTER Qualifiers