SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-49
Calculating Totals
The next command sets all style options to their default values:
>> RESET STYLE *;
Calculating Totals
You can calculate totals of print-item values. The values must be numeric. A total
appears at the end of the report. For example, suppose you want to total the quantity
ordered of all parts in the following report:
>> SET LIST_COUNT 0;
>> SELECT PARTNUM, SUM (QTY_ORDERED)
+> FROM SALES.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-list).
In the next example, the columns that contain expressions are assigned detail alias
names. These columns are totaled at the end of the report.
>> VOLUME INVENT;
>> SET LIST_COUNT 0;
>> SELECT S.SUPPNUM, SUPPNAME, CITY, STATE, P.PARTNUM,
+> QTY_AVAILABLE, PARTCOST, PRICE