Enterprise PeopleTools 8.51 PeopleBook: SQR for PeopleSoft Developers

Creating Master and Detail Reports

This chapter provides overviews of master and detail reports and the sample program for master and detail reports and discusses how to correlate subqueries:

Understanding Master and Detail Reports

Master and detail reports show hierarchical information. The information is normally retrieved from multiple tables that have a one-to-many relationship, such as customers and orders. The customer information is the master and the orders are the detail.

Often, you can obtain such information with a single SQR select paragraph. In such a program, the data from the master table is joined with data from the detail table. You can implement break logic to group the detail records for each master record. This type of report has one major disadvantage: if a master record has no associated detail records, it is not displayed. If you need to show all master records, whether they have detail records or not, this type of report will not meet your needs.

See Using Break Logic.

To show all master records, whether or not they have detail records, create a master and detail report with one SELECT statement that retrieves records from the master table, followed by separate SELECT statements that retrieve the detail records that are associated with each master record.

The sample program in this chapter produces just such a report. In the example, one BEGIN-SELECT returns the names of customers. For each customer, two additional BEGIN-SELECT commands are run—one to retrieve order information and another to retrieve payment information.

When one query returns master information and another query returns detail information, the detail query is nested within the master query.

Understanding the Sample Program for Master and Detail Reports

In the sample program, the nested queries are invoked once for each customer, each one retrieving records that correspond to the current customer. A bind variable correlates the subqueries in the WHERE clause. This variable correlates the customer number (cust_num) with the current customer record.

Program ex7a.sqr
begin-program
  do main
end-program
begin-procedure main
begin-select
  Print 'Customer Information' (,1)
  Print '-'                    (+1,1,45) Fill
name   (+1,1,25)
city   (,+1,16)
state  (,+1,2)
cust_num
  do cash_receipts(&cust_num)
  do orders(&cust_num)
  position (+2,1)
from customers
end-select
end-procedure ! main
begin-procedure cash_receipts (#cust_num)
  let #any = 0
begin-select
  if not #any
     print 'Cash Received' (+2,10)
     print '-------------' (+1,10)
     let #any = 1
  end-if
date_received    (+1,10,20) edit 'DD-MON-YY'
amount_received  (,+1,13) Edit $$$$,$$0.99
from cash_receipts a
where a.cust_num = #cust_num
end-select
end-procedure ! cash_receipts
begin-procedure orders (#cust_num)
  let #any = 0
begin-select
  if not #any
    print 'Orders Booked' (+2,10)
    print '-------------' (+1,10)
    let #any = 1
  end-if
a.order_num
order_date                (+1,10,20) Edit 'DD-MON-YY'
description               (,+1,20)
c.price * b.quantity      (,+1,13) Edit $$$$,$$0.99
from  orders a, ordlines b, products c
where a.order_num = b.order_num
  and b.product_code = c.product_code
  and a.cust_num = #cust_num
end-select
end-procedure ! orders
begin-heading 3
 print $current-date (1,1) Edit 'DD-MON-YYYY'
 page-number (1,69) 'Page ' 
end-heading

Correlating Subqueries

The ex7a.sqr sample program contains three procedures—main, cash_receipts, and orders—which correspond to the three queries. The main procedure is the master. It retrieves the customer names. For each customer, we the program invokes the cash_receipts procedure to list the cash receipts, if any, and orders to list the customer’s orders, if any.

The procedures take the cust_num variable as an argument. As you can see, cash_receipts and orders are called many times, once for each customer. Each time, the procedures perform the same query with a different value for the cust_num variable in the WHERE clause.

Note the use of the IF command and the #any numeric variable in these procedures. When the BEGIN-SELECT command returns no records, SQR does not process the following PRINT commands. Thus, the headings for these procedures are displayed only for those customers who have records in the detail tables.

The orders procedure demonstrates the use of an expression in the BEGIN-SELECT. The expression is c.price * b.quantity.

Note. Examine the format of the dollar amount with the argument EDIT $$$$,$$0.99. This format uses a “floating-to-the-right” money symbol. If fewer digits are used than the six that we specified here, the dollar sign floats to the right and remains close to the number.

See Using Procedures and Local Variables and Passing Arguments.

Sample Program Output

The following is the output for for program ex7a.sqr.

6-APR-2004 Page 1 Customer Information --------------------------------------------- Gregory Stonehaven Everretsville OH Cash Received ------------- 01-FEB-03 $130.00 Customer Information --------------------------------------------- John Conway New York NY Cash Received ------------- 01-MAR-03 $140.00 Customer Information --------------------------------------------- Eliot Richards Queens NY Cash Received ------------- 16-JAN-03 $220.12 17-JAN-03 $260.00 Orders Booked ------------- 02-MAY-03 Whirlybobs $239.19 02-MAY-03 Canisters $3,980.25 Customer Information --------------------------------------------- Isaiah J Schwartz and Com Zanesville OH Cash Received ------------- 18-JAN-03 $190.00 02-JAN-03 $1,100.00 Orders Booked ------------- 02-MAY-03 Hop scotch kits $6,902.00 02-MAY-03 Wire rings $19,872.90 Customer Information --------------------------------------------- Harold Alexander Fink Davenport IN Cash Received ------------- 01-FEB-03 $1,200.00 01-MAR-03 $1,300.00 Orders Booked ------------- 19-MAY-03 Ginger snaps $44.28 19-MAY-03 Modeling clay $517.05