SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-15
Organizing Rows Into Break Groups
+> PARTCOST HEADING 'Unit Cost',
+> PARTCOST * QTY_AVAILABLE HEADING 'Total Cost',
+> QTY_AVAILABLE * (PRICE - PARTCOST)
+> HEADING 'Profit'
+> NAME PROFIT;
To refer to the PROFIT column, enter:
S> TOTAL PROFIT;
You cannot refer to a detail alias name within the DETAIL command itself. You can only
define the name there and use it in succeeding commands.
Organizing Rows Into Break Groups
In addition to grouping rows for aggregate function calculations, you can group rows
into break groups. The important differences between these two types of groups are:
•
For a group formed by a GROUP BY clause:
°
The result is one row; the row is a combination of all rows in the group.
°
Typically, each item in the select list either has the same value in all the rows of
the group or is an aggregate function applied to the values in the rows of the
group. See Grouping Data for Calculations on page 3-16.
•
For a break group:
°
The group is formed by specifying an ORDER BY clause and BREAK ON
command.
°
In a break group, rows are not combined. Each row appears in the report.
°
The rows are grouped together, and the break column value appears only once
unless a page break occurs in the middle of the break group.
°
You can print a title before the break group rows and print a footing following
the rows.
°
You can calculate subtotals following a break group.
You can divide a break group into more break groups. This example illustrates a report
defined with three levels of break groups. The commands that define the report are:
>> SELECT SALESREP, C.CUSTNUM, R.ORDERNUM, PARTNUM,
+> UNIT_PRICE, QTY_ORDERED
+> FROM CUSTOMER C, ORDERS R, ODETAIL OD
+> WHERE C.CUSTNUM = R.CUSTNUM
+> AND R.ORDERNUM = OD.ORDERNUM
+> ORDER BY SALESREP, C.CUSTNUM, R.ORDERNUM, PARTNUM;
S> DETAIL SALESREP AS I4 HEADING 'SREP',
+> CUSTNUM,
+> ORDERNUM AS I8,
+> PARTNUM,
+> UNIT_PRICE,