SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-52
Calculating Subtotals
+> SAMDBCAT.INVENT.SUPPLIER S,
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM
+> ORDER BY S.SUPPNUM, P.PARTNUM;
S> DETAIL PARTNUM AS I6 HEADING 'Part/Number' CENTER,
+> QTY_AVAILABLE AS I9 HEADING 'Available/Units' CENTER,
+> PARTCOST AS F11.2 HEADING 'Unit Cost/(dollars)' CENTER,
+> PARTCOST * QTY_AVAILABLE AS F14.2
+> HEADING 'Total Cost/(dollars)' CENTER
+> NAME TOTAL_COST,
+> QTY_AVAILABLE * (PRICE - PARTCOST) AS F11.2
+> HEADING 'Estimated/Profit' CENTER
+> NAME PROFIT;
S> BREAK ON S.SUPPNUM;
S> BREAK TITLE S.SUPPNUM
+> ('Supplier:', S.SUPPNUM,
+> CONCAT (SUPPNAME STRIP, ', ',CITY STRIP, ', ',
+> STATE),
+> SKIP 1) ;
S> SUBTOTAL TOTAL_COST, PROFIT OVER S.SUPPNUM;
+> TOTAL TOTAL_COST, PROFIT;
S> PAGE TITLE 'Supplier Parts Summary' CENTER;
S> REPORT TITLE 'Date: ',
S> CURRENT_TIMESTAMP AS DATE “MA DB2, Y4”,
+> TAB 48,
+> 'Time: ', CURRENT_TIMESTAMP AS TIME *;
S> REPORT FOOTING 'End of Summary' CENTER;
S> PAGE FOOTING TAB 58, 'Page ', PAGE_NUMBER AS I3;
S> LIST ALL;
Figure 4-19 and Figure 4-20 show the resulting two-page report.