Enterprise PeopleTools 8.51 PeopleBook: SQR for PeopleSoft Developers

Creating Multiple Reports from One Program

This chapter provides overviews of how to create multiple reports and the sample program for multiple reports and discusses how to:

Define heading and footing sections.
Define program output.

Understanding How to Create Multiple Reports

You can create multiple reports based on common data, selecting the database records only once and creating different reports simultaneously. The alternative—writing separate programs for the different reports—would require you to perform a separate database query for each report. Repeated queries are costly because database operations are often the most resource-consuming or time-consuming part of creating a report. Creating multiple reports from one program can save a significant amount of processing time.

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 chapter “Printing Mailing Labels,” the form letter from “Creating Form Letters” and the listing report from “Selecting Data from the Database.” 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. It 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, print_letter prints one letter, and print_listing 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.

Defining Heading and Footing Sections

SQR enables you to define HEADING and FOOTING sections for each report. This sample program defines only the heading and footing for the listing report because the other two reports do not use them. The FOR-REPORTS option of the BEGIN-HEADING and BEGIN-FOOTING commands specifies the report name . The parentheses are required. The USE-REPORT command is not needed in the heading or footing. The report is implied by the FOR-REPORTS option.

Defining Program Output

Most of the code for ex18a.sqr is taken from ex9a.sqr, ex10a.sqr, and ex3a.sqr. Because this program creates output with proportional fonts, you must run it with the -KEEP or -PRINTER:xx command-line flags.

When you run ex18a.sqr, you get three output files that match the output files for ex9a, ex10a, and ex3a, respectively. These output files have the names ex18a.lis (labels), ex18a.l01 (form letter), and ex18a.l02 (customer listing). If you specify -KEEP, the output files are named ex18a.spf, ex18a.s01, and ex18a.s02, respectively.