HP 3000 Manuals HP 3000 Manuals
Chapter 8 Special Topics
Interface to Report/V
In previous sections, we looked at examples of using Transact as a report
generator. These examples illustrated features of Transact but were not
meant to indicate that one of the strengths of Transact is its report
writing capability.
In fact, Transact is not meant to be a report writer. Although some high
level benefits are gained, most reporting controls, like sort control
breaks and subtotals, still have to be coded in Transact. This is where
Report/V can help out.
Report/V is a report writer. It can be run as a free standing program
and most times this is the way it is run.
However for complex reporting needs, combining Transact and Report/V will
solve the problem. Transact can be used to extract the data and do the
data manipulation creating a file which is then given to Report/V to
generate the report.
The following example illustrates a typical Transact solution to a
complicated reporting requirement. For those of you who use report
writers, think about what you would have to do if you needed to prepare a
report like this. If the report writer you are familiar with could
prepare this report, the chances are it would consist of several
intermediate passes over the data before the final report could be
generated.
More typically, you would have to do the data extraction and manipulation
using a language like COBOL and would probably let COBOL create the
report, since interfacing to a report writer at that point would be
difficult.
The example consists of preparing a backorder report. Our database
maintains inventory of each part-number by location code. Thus it is
possible that there are many records to add up to compute the total
inventory on hand for a part. Likewise, there may be many orders for the
same part-number. For illustration purposes, inventory is arbitrarily
allocated to orders based upon the order-date. Thus, the backlog report
will identify which orders or parts of an order can be filled from
inventory and which can only be filled from future production.
Assume that the database contains the following inventory and order
information.
_____________________________________
| |
| INVENTORY DETAILS |
| PART-NUMBER LOCATION QUANTITY |
| ------------------------------|
| PART1 LOC1 100 |
| LOC2 200 |
| LOC3 300 |
| PART2 LOC1 100 |
| PART3 LOC2 200 |
_____________________________________
Figure 8-1. Part number balances by location
________________________________________________________________
| |
| ORDER DETAILS |
| ORDER-NO CUST-NO ORDER-DATE LINE-NO PART-NUMBER QUANTITY |
| ---------------------------------------------------------|
| ORDER1 1000 850101 10 PART1 100 |
| 20 PART2 200 |
| 30 PART3 300 |
| |
| ORDER2 2000 850102 10 PART3 200 |
| 20 PART2 300 |
| 30 PART1 400 |
| |
| ORDER3 1000 850103 10 PART1 300 |
| 20 PART2 400 |
| 30 PART3 500 |
________________________________________________________________
Figure 8-2. Part number open orders
The report looks like this:
____________________________________________________________________________________
| |
| BACKLOG DETAIL BY CUSTOMER AND PART |
| |
| CUSTOMER PART NUMBER ORDER DATE LINE ORDERED BACKORDERED|
| |
| 1000 PART1 ORDER1 85/01/01 10 100 |
| |
| 1000 PART2 600 500 |
| |
| PART3 ORDER1 85/01/01 30 300 100 |
| ORDER3 85/01/03 30 500 500 |
| 1000 PART3 800 600 |
| |
| 1000 TOTAL 1,800 1,300 |
| |
| 2000 PART1 ORDER2 85/01/02 30 400 |
| 2000 PART1 400 |
| |
| PART2 ORDER2 85/01/02 20 300 300 |
| 2000 PART2 300 300 |
| |
| 2000 PART3 ORDER2 85/01/02 10 200 200 |
| 2000 PART3 200 200 |
| |
| 2000 TOTAL 900 500 |
| |
| GRAND TOTAL 2,700 1,800 |
____________________________________________________________________________________
Figure 8-3. Backlog detail by customer and part
Note that the inventory was applied to the oldest orders based on
order-date.
The Transact and Report/V program that generated this report follow.
_________________________________________________________________________________
| |
| 1 system ex65,base=orders,file=shortage(update) |
| 2 ,file=temp(sort); |
| 3 define(item) tot-inv i(6): |
| 4 inv-part x(8): |
| 5 inv-quantity i(6),alias=(quantity(inventory)); |
| 6 list part-number: |
| 7 cust-no: |
| 8 order-date: |
| 9 order-no: |
| 10 line-no: |
| 11 quantity: |
| 12 back-order: |
| 13 tot-inv: |
| 14 inv-part: |
| 15 inv-quantity; |
| 16 find(serial) orderline,list=(part-number,order-no,line-no,quantity)|
| 17 ,perform=each-orderline; |
| 18 find(serial) temp,list=(part-number:quantity) |
| 19 ,sort=(part-number,order-date) |
| 20 ,perform=each-temp-orderline; |
| 21 call ex65r,report; |
| 22 exit; |
| 23 |
| 24 each-orderline: |
| 25 |
| 26 set(key) list (order-no); |
| 27 find(chain) orderhead,list=(cust-no,order-date); |
| 28 put temp,list=(part-number:quantity); |
| 29 return; |
_________________________________________________________________________________
Figure 8-4. Transact program to create backlog report
___________________________________________________________________________
| |
| 31 each-temp-orderline: |
| 32 |
| 33 if (part-number) <> (inv-part) |
| 34 then perform each-inv-part; |
| 35 let (tot-inv) = (tot-inv) - (quantity); |
| 36 if (tot-inv) < 0 |
| 37 then |
| 38 do |
| 39 let (back-order) = 0 - (tot-inv); |
| 40 let (tot-inv) = 0; |
| 41 doend |
| 42 else let (back-order) = 0; |
| 43 put shortage,list=(cust-no,part-number,order-no,order-date,|
| 44 line-no,quantity,back-order); |
| 45 return; |
| 46 |
| 47 each-inv-part: |
| 48 |
| 49 move (inv-part) = (part-number); |
| 50 set(key) list (part-number); |
| 51 let (tot-inv) = 0; |
| 52 find(chain) inventory,list=(inv-quantity) |
| 53 ,perform=accum-inv; |
| 54 return; |
| 55 |
| 56 accum-inv: |
| 57 |
| 58 let (tot-inv) = (tot-inv) + (inv-quantity); |
| 59 return; |
___________________________________________________________________________
Figure 8-4. Transact program to create backlog report (cont
____________________________________________________________________________
| |
| 1 report ex65r; |
| 2 option nohead; |
| 3 access shortage,list=(cust-no,part-number,order-no,order-date,|
| 4 line-no,quantity,back-order); |
| 5 sort(1) cust-no:part-number; |
| 6 page heading "BACKLOG DETAIL BY CUSTOMER AND PART",col=25: |
| 7 "CUSTOMER",LINE=2,COL=1: |
| 8 "PART NUMBER",COL=10: |
| 9 "ORDER",COL=22: |
| 10 "DATE",COL=32: |
| 11 "LINE",COL=42: |
| 12 "ORDERED",COL=50: |
| 13 "BACKORDERED",COL=70: |
| 14 " ",line=1; |
| 15 detail cust-no,col=1: |
| 16 part-number,col=10: |
| 17 order-no,col=22: |
| 18 order-date,col=32,edit="^^^^/^^^^/^^^^": |
| 19 line-no,col=43: |
| 20 quantity,col=50,edit="ZZZ,ZZZ": |
| 21 back-order,col=70,edit="ZZZ,ZZZ"; |
| 22 group(2) summary cust-no,col=1: |
| 23 part-number,col=10: |
| 24 total(quantity),col=50,edit="ZZZ,ZZZ": |
| 25 total(back-order),col=70,edit="ZZZ,ZZZ": |
| 26 " ",line=1; |
| 27 group(1) summary cust-no,col=1: |
| 28 "TOTAL",col=10: |
| 29 total(quantity),col=50,edit="ZZZ,ZZZ": |
| 30 total(back-order),col=70,edit="ZZZ,ZZZ": |
| 31 " ",line=1; |
| 32 report summary "GRAND TOTAL",col=1: |
| 33 total(quantity),col=50,edit="ZZZ,ZZZ": |
| 34 total(back-order),col=70,edit="ZZZ,ZZZ"; |
____________________________________________________________________________
Figure 8-5. Report/V program to create backlog report
The Transact program extracts the data desired and writes it to an MPE
file called shortage. When it is finished, it calls the report program
in line 21.
That's all there is to it. There is a similar interface between Transact
and Inform. If the report were defined using Inform, line 21 would
become:
call ex65r,inform;
The example above took advantage of Dictionary/V by defining the file
shortage in the dictionary. When interfacing with Report/V, the file
does not have to be defined in the dictionary nor do the data elements
that are contained in the file have to be defined in the dictionary.
We have already seen how to use Transact without the dictionary. If the
data items were defined in the dictionary, but the file was not, there
would not be any changes required to run the above program.
If the data items were not defined in the dictionary, then the report
program would have to contain the item definitions using DEFINE(ITEM).
The resulting Report/V program is shown below.
____________________________________________________________________________
| |
| 1 report ex65r; |
| 2 option nohead; |
| 2.1 define(item) cust-no 9(4): |
| 2.2 part-number x(8): |
| 2.3 order-no x(8): |
| 2.4 order-date x(6): |
| 2.5 line-no 9(2): |
| 2.6 quantity i(6): |
| 2.7 back-order i(6); |
| 3 access shortage,list=(cust-no,part-number,order-no,order-date,|
| 4 line-no,quantity,back-order); |
| 5 sort(1) cust-no:part-number; |
| 6 page heading "BACKLOG DETAIL BY CUSTOMER AND PART",col=25: |
| 7 "CUSTOMER",LINE=2,COL=1: |
| 8 "PART NUMBER",COL=10: |
| 9 "ORDER",COL=22: |
| 10 "DATE",COL=32: |
| 11 "LINE",COL=42: |
| 12 "ORDERED",COL=50: |
| 13 "BACKORDERED",COL=70: |
| 14 " ",line=1; |
| 15 detail cust-no,col=1: |
| 16 part-number,col=10: |
| 17 order-no,col=22: |
| 18 order-date,col=32,edit="^^^^/^^^^/^^^^": |
| 19 line-no,col=43: |
| 20 quantity,col=50,edit="ZZZ,ZZZ": |
| 21 back-order,col=70,edit="ZZZ,ZZZ"; |
| 22 group(2) summary cust-no,col=1: |
| 23 part-number,col=10: |
| 24 total(quantity),col=50,edit="ZZZ,ZZZ": |
| 25 total(back-order),col=70,edit="ZZZ,ZZZ": |
| 26 " ",line=1; |
| 27 group(1) summary cust-no,col=1: |
| 28 "TOTAL",col=10: |
| 29 total(quantity),col=50,edit="ZZZ,ZZZ": |
| 30 total(back-order),col=70,edit="ZZZ,ZZZ": |
| 31 " ",line=1; |
| 32 report summary "GRAND TOTAL",col=1: |
| 33 total(quantity),col=50,edit="ZZZ,ZZZ": |
| 34 total(back-order),col=70,edit="ZZZ,ZZZ"; |
____________________________________________________________________________
Figure 8-6. Using Report/V without the dictionary