Using Break Processing Enhancements

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  (301) 555-3645



          Harry's Landmark Diner
          17043 Silverfish Road
          South Park
          Miningville  (317) 555-0948   
				  


Total Customers for State:        2



MI        Sam Johnson
          37 Cleaver Street
          Sandy Acres
          Bell Harbor  (313) 555-6732   


	Total Customers for State:        1

NH        Jerry's Junkyard Specialties
          Crazy Lakes Cottages
          Rural Delivery #27
          Frogline  (612) 555-2877   


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

If a page break occurs within a group, you may want to reprint headings and the value of the break column at the top of the new page.

To control the printing of the value, use PRINT=CHANGE/TOP-PAGE. With this qualifier, the value of the ON-BREAK column is printed when it changes and after every page break. In this example, the value of state is printed not only when it changes, but whenever the report starts a new page.

To format records, use the NEXT-LISTING command. This command serves two purposes: the SKIPLINES=1 argument skips one line between records and then renumbers the current line as line 1; the NEED=4 argument prevents a listing from being split over two pages by specifying the minimum number of lines that are needed to write a new listing on the current page. In this case, if fewer than four lines are left on a page, SQR starts a new page.

In the HEADING section, the reserved variable $current-date prints the date and the time. This variable is initialized with the date and time of the client machine when the program starts to run. SQR provides predefined, or reserved, variables for a variety of uses.

In this example, the complete command is PRINT $current-date (1,1) EDIT 'DD/Mon/YYYY'. It prints the date and time at position 1,1 of the heading. The EDIT argument specifies an edit mask, or format, for printing the date. SQR provides a variety of edit masks for use in formatting numbers, dates, and strings.

See PRINT.

Note that the PRINT command for the report title precedes the command for the $current-date reserved variable, even though the date is on the left and the title is on the right. SQR always assembles a page in memory before printing, so the order of these commands does not matter if you use the correct print position qualifiers.

The last two commands in the HEADING section print a string of hyphens under the column headings. Note the use of the FILL option with the PRINT command. This option tells SQR to fill the specified width with this pattern, which is a useful method to print a line.

The FOOTING section prints Page n of m as in earlier examples.

The ex5d.sqr program also prints a subtotal of customers in each state and a grand total of all customers. These calculations are performed with two numeric variables, one for the subtotal and one for the grand total. These variables are:

• #state_total

• #cust_total

SQR for PeopleSoft has a small set of variable types. The most common types are numeric variables and string variables. All numeric variables in SQR are preceded by a pound sign (#), and all string variables are preceded by a dollar sign ($). An additional SQR variable type is the date variable.

In SQR for PeopleSoft, numeric and string variables are not explicitly declared. Instead, they are implicitly defined by their first use. All numeric variables start out as zero and all string variables start out as null, so they do not need to be initialized. The string variables are of varying length and can hold long and short strings of characters. Assigning a new value to a string variable automatically adjusts its length.

In the list_customers procedure, #state_total and #cust_total are set to zero at the beginning of the procedure. This initialization is optional and is done for clarity only. The #state_total variable increments by 1 for every row that is selected.

When the value of state changes, the program calls the state_tot procedure and prints the value of #state_total. Note the use of the EDIT 999,999 edit mask, which formats the number.

This procedure also employs the LET command. LET is the assignment command in SQR for building complex expressions. Here, LET adds the value of #state_total to #cust_total. At the end of the procedure, #state_total is reset to zero.

The list_customers procedure contains an example of the SQR if-then-else logic. The condition starts with IF followed by an expression. If the expression evaluates to true or to a number other than zero, the subsequent commands are run. Otherwise, if the IF command has an ELSE command, then those commands are run. IF commands always end with an END-IF command.

In ex5d.sqr, the value of #cust_total is examined. If it is greater than zero, the query has returned rows of data, and the program prints the string Total Customers: and the value of #cust_total.

If #cust_total is zero, the query has not returned any data. In that case, the program prints the string No customers.

Many SQR commands, such as BEGIN-PROGRAM and BEGIN-SELECT, use a hyphen, whereas procedure and variable names use an underscore.

Procedure and variable names can contain either a hyphen or underscore, but you should use underscores in procedure and variable names to distinguish them from SQR commands. Doing so also prevents confusion when you mix variable names and numbers in an expression, where hyphens could be mistaken for minus signs.