SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-50
Calculating Totals
+> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM
+> AND P.PARTNUM IN (5100, 5101, 5103)
+> ORDER BY S.SUPPNUM, P.PARTNUM;
S> DETAIL PARTNUM,
+> PARTCOST AS F8.2,
+> PARTCOST * QTY_AVAILABLE NAME TOTAL_COST,
+> QTY_AVAILABLE * (PRICE - PARTCOST) NAME PROFIT;
+> TOTAL TOTAL_COST, PROFIT;
S> LIST ALL;
Figure 4-18
shows the resulting report.
Totals are printed in a default format. You can override the size of the field and the
format of the value by using an AS clause for the print item in the DETAIL command.
For example, suppose you want to specify smaller fields for the last two columns of the
report:
S> DETAIL PARTNUM,
+> PARTCOST,
+> PARTCOST * QTY_AVAILABLE AS F14.2
+> NAME TOTAL_COST,
+> QTY_AVAILABLE * (PRICE - PARTCOST) AS F12.2
+> NAME PROFIT;
+> TOTAL TOTAL_COST, PROFIT;
S> LIST ALL;
Figure 4-19 shows the resulting report.
Figure 4-18. Example of a Report With Totals
--- 7 row(s) selected.
>>
PARTNUM PARTCOST TOTAL_COST PROFIT
5100
5100
5101
5103
5100
5101
5103
100.00
105.00
135.00
265.00
95.00
125.00
250.00
323700.00
339885.00
324000.00
832000.00
307515.00
300000.00
881920.00
161850.00
145665.00
156000.00
449280.00
178035.00
180000.00
499200.00
3309020.00 1770030.00
VST0418.vsd