Purpose
You can accumulate subtotals of a temporary result in the same way you accumulate subtotals of a database column.
Operation
Select one or more control break columns by enclosing their names in parentheses in a SORT statement and then enclose column names in the PRINT statement in parentheses where you want to show totals of those columns at control breaks or at the end of the report.
Sample
The following sample query illustrates the use of the PRINT statement to accumulate subtotals for results from a SET statement in the query.
FIND 10 CAI-SLSHST-REC ROWS
WITH SHIP-QTY > 100
RELATED BY SLMN-ID TO CAI-ORDERS-REC
SET NET-COST (8.2) = CAI-SLSHST-REC SHIP-QTY * UNIT-PRICE
SET DISCOUNT (5.2) = NET-COST * DISC-PCT
SORT CAI-ORDERS-REC BY (ORD-ID) AND
CAI-SLSHST-REC BY ITM-ID
PRINT FROM CAI-ORDERS-REC ORD-ID
FROM CAI-SLSHST-REC ITM-ID
SHIP-QTY
UNIT-PRICE
(NET-COST)
DISCOUNT
Explanation
The preceding query specifies that NET-COST is to be accumulated by enclosing that computed column in parentheses in the PRINT statement. In the SORT statement, ORD-ID is enclosed in parentheses to indicate that it is a control break column. A subtotal for NET-COST will be shown each time the value in the ORD-ID column changes.
Output
Following is the output after executing the preceding query.
=> 01/02/2010 CA Dataquery PAGE 1 15:31:50 DETAIL ORD-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ---------- -------- ---------- ------------- --------- 08811 O50006 0000203 00014.75 00002994.25 00010.00 G00222 0000100 00100.00 00010000.00 00000.00 910011 0001000 00100.00 00100000.00 00020.00 TOTAL ORD-ID 08811 * 00112994.25 * GRAND TOTAL * 00112994.25 -------------------------------- LAST PAGE --------------------------------- <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
Explanation
In the preceding output screen the values contained in ORD-ID appear only once in the ORD-ID column. This is a result of setting the SUPPRESS DUPLICATE COLUMNS option to YES in your User Profile. (See the CA Dataquery User Guide for more information.)
Subtotals are designated by TOTAL, followed by the specific control break column (ORD-ID) and the current value contained in the column. The SUBTOTAL value is preceded by an asterisk. The total is designated by * GRAND TOTAL. An asterisk also precedes the accumulated value.
|
Copyright © 2014 CA.
All rights reserved.
|
|