SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-49
Calculating Totals
To set all style options to their default values, enter:
>> RESET STYLE *;
Calculating Totals
You can calculate totals of numeric print-item values. A total appears at the end of the
report. For example, in the following report, you want the total of all parts ordered:
>> SELECT PARTNUM, SUM (QTY_ORDERED)
+> FROM ODETAIL
+> GROUP BY PARTNUM;
S> TOTAL COL 2;
S> LIST ALL;
PARTNUM (EXPR)
------- -------------------
212 20
244 47
255 38
. .
. .
. .
7102 18
7301 96
-------------------
-------------------
1406
--- 29 row(s) selected.
In a TOTAL command, you can refer to an item of the select list by column name, alias
name, or column number. You can reference a print item of the detail line by column
name or detail alias name.
Only one TOTAL command is in effect at a time. If you want to compute totals for more
than one item, you must specify all the items in one command. You can edit the TOTAL
command by using FC, replace the command by reentering it, delete the command by
using RESET REPORT TOTAL, or modify the TOTAL command by using RESET
REPORT TOTAL (column).
In this example, the columns that contain expressions are assigned detail alias
names. These columns are totaled at the end of the report.
>> SELECT S.SUPPNUM, SUPPNAME, CITY, STATE, P.PARTNUM,
+> QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SAMDBCAT.SALES.PARTS P,
+> SAMDBCAT.INVENT.PARTSUPP PS,
+> SAMDBCAT.INVENT.SUPPLIER S,
+> WHERE P.PARTNUM = PS.PARTNUM