SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-51
Calculating Subtotals
The display format you specify must be large enough to contain the result of the
TOTAL command.
You cannot use an AS clause to format a value of the INTERVAL data type. These
values must be displayed or printed in the default format. If you calculate a total (or
subtotal) of INTERVAL values, the resulting value might not fit in the field for the
default format. If you plan to compute totals for a column of this data type, the column
definition specified when the table is created should include a precision large enough
to contain the total values. For example, even though the column will contain only
2-digit values, the precision might be 6 digits (or more) to accommodate total values.
Calculating Subtotals
You can calculate subtotals for one or more columns each time a break point occurs.
Use the ORDER BY clause of the SELECT command and the BREAK ON command
to define the break groups. For more information about these commands, see
Organizing Rows Into Break Groups
on page 4-14.
In the supplier parts summary report in Figure 4-19
, subtotals are computed for the
total cost and estimated profit. The report is defined with the following commands:
>> VOLUME INVENT;
>> SET LIST_COUNT 0;
>> SET LAYOUT LEFT_MARGIN 8, RIGHT_MARGIN 72,
+> PAGE_LENGTH 56;
>> SELECT S.SUPPNUM, SUPPNAME, CITY, STATE, P.PARTNUM,
+> QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM
Figure 4-19. Example of Formatted Total Values
VST0419.vsd
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
--- 7 row(s) selected.
>>