Understanding the Sample Program for Multiple Reports
The following sample program, ex18a.sqr, shows how SQR for PeopleSoft enables you to write multiple reports with different layouts and different heading and footing sections. The sample program prints three reports: the labels from the “Printing Mailing Labels” topic, the form letter from the “Creating Form Letters” topic, and the listing report from the “Selecting Data from the Database” topic. All three reports are based on the same data.
Program ex18a.sqr
#define MAX_LABEL_LINES 10
#define LINES_BETWEEN_LABELS 3
begin-setup
declare-layout labels
paper-size=(10,11) left-margin=0.33
end-declare
declare-layout form_letter
end-declare
declare-layout listing
end-declare
declare-report labels
layout=labels
end-declare
declare-report form_letter
layout=form_letter
end-declare
declare-report listing
layout=listing
end-declare
end-setup
begin-program
do main
end-program
begin-procedure main
do init_mailing_labels
begin-select
name
addr1
addr2
city
state
zip
move &zip to $zip xxxxx-xxxx
phone
do print_label
do print_letter
do print_listing
from customers
end-select
do end_mailing_labels
end-procedure ! main
begin-procedure init_mailing_labels
let #label_count = 0
let #label_lines = 0
use-report labels
columns 1 29 57 ! enable columns
alter-printer font=5 point-size=10
end-procedure ! init_mailing_labels
begin-procedure print_label
use-report labels
print &name (1,1,30)
print &addr1 (2,1,30)
let $last_line = &city || ', ' || &state || ' ' || $zip
print $last_line (3,1,30)
next-column at-end=newline
add 1 to #label_count
if #current-column = 1
add 1 to #label_lines
if #label_lines = {MAX_LABEL_LINES}
new-page
let #label_lines = 0
else
next-listing no-advance skiplines={LINES_BETWEEN_LABELS}
end-if
end-if
end-procedure ! print_label
begin-procedure end_mailing_labels
use-report labels
use-column 0 ! disable columns
new-page
print 'Labels printed on ' (,1)
print $current-date ()
print 'Total labels printed = ' (+1,1)
print #label_count () edit 9,999,999
end-procedure ! end_mailing_labels
begin-procedure print_letter
use-report form_letter
begin-document (1,1)
&name
&addr1
&addr2
@city_state_zip
.b
.b
$current-date
Dear Sir or Madam:
.b
Thank you for your recent purchases from ACME Inc. We would
like to tell you about our limited time offer. During this month,
our entire inventory is marked down by 25%. Yes, you can buy your
favorite merchandise and save too.
To place an order simply dial 800-555-ACME.
Delivery is free too, so don't wait.
.b
.b
Sincerely,
Clark Axelotle
ACME Inc.
end-document
position () @city_state_zip
print &city ()
print ', ' ()
print &state ()
print ' ' ()
move &zip to $zip xxxxx-xxxx
print $zip ()
new-page
end-procedure ! print_letter
begin-heading 4 for-reports=(listing)
print 'Customer Listing' (1) center
print 'Name' (3,1)
print 'City' (,32)
print 'State' (,49)
print 'Phone' (,55)
end-heading
begin-footing 1 for-reports=(listing)
! Print "Page n of m" in the footing
page-number (1,1) 'Page '
last-page () ' of '
end-footing
begin-procedure print_listing
use-report listing
print &name (,1)
print &city (,32)
print &state (,49)
print &phone (,55)
position (+1)
end-procedure ! print_listing
The SETUP section defines three layouts and three different reports that use these layouts. The labels report requires a layout that is different from the default. The other two reports use a layout that is identical to the default layout. You can save the last layout declaration and use the form letter layout for the listing. However, unless a logical reason exists why the two layouts should be the same, you should keep separate layouts. The name of the layout indicates which report uses it.
The main procedure performs the Select command. The <command> is performed only once and includes all of the columns for all of the reports. The phone column is used only in the listing report, and the addr2 column is used only in the form letter report. The other columns are used in more than one report.
For each record that is selected, three procedures are run. Each procedure processes one record for its corresponding report. The print_label procedure prints one label, the print_letter procedure prints one letter, and the print_listing procedure prints one line in the listing report. Each procedure begins by setting the SQR printing context to its corresponding report. SQR sets the printing context with the USE-REPORT command.