SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-55
Defining a Subtotal Label
•
The OVER clause indicates when a subtotal is to be calculated. If you omit the
OVER clause, the subtotals appear whenever any data value in any currently
defined break column changes. Only one SUBTOTAL command without an OVER
clause is in effect at one time.
•
To delete all subtotal commands, enter:
S> RESET REPORT SUBTOTAL;
RESET REPORT BREAK also resets subtotals by deleting the specified break
columns. You can change a SUBTOTAL command with the FC command, replace
the command by reentering it with the same OVER specification, or modify the
SUBTOTAL command by using RESET REPORT SUBTOTAL (column-list).
•
Entering a SUBTOTAL command returns you to the first row of selected output.
•
You should make the display format for the column large enough to accommodate
the subtotal values.
Defining a Subtotal Label
A subtotal label appears in the break column when the subtotal is calculated. In the
report in Figure 4-20, no subtotal labels appear because the break column SUPPNUM
does not appear in the detail line. When the value of SUPPNUM changes, the subtotal
is calculated, but no label appears.
The report in Figure 4-21 summarizes current orders. Subtotals are calculated over
more than one break group.
The default subtotal label is an asterisk (*). You can set a different default label with the
SET STYLE command. For example, to set the label used in Figure 4-21:
>> SET STYLE SUBTOTAL_LABEL '* * *';
You can specify from 0 through 255 single-byte characters (or 0 through 177 double-
byte characters) in the label. To turn off subtotal labeling, specify zero characters:
>> SET STYLE SUBTOTAL_LABEL '';
The label you specify applies to all subtotals. If the label does not fit in the column, the
string is truncated.
To define the report in Figure 4-21:
>> SELECT * FROM ORDERS O, ODETAIL OD
+> WHERE O.ORDERNUM = OD.ORDERNUM
+> ORDER BY CUSTNUM, OD.ORDERNUM;
S> DETAIL CUSTNUM,
+> OD.ORDERNUM,
+> PARTNUM,
+> UNIT_PRICE AS F8.2 HEADING 'PRICE',
Note. If you are defining a report that contains double-byte characters, see Printing Double-
Byte Characters on page 4-58 for special considerations regarding defining subtotal labels.