SQL/MP Report Writer Guide
Table Of Contents
- What’s New in This Manual
- About This Manual
- 1 Introduction to the NonStop SQL/MP Report Writer
- 2 Using SQLCI and the Report Writer
- 3 Selecting Data for a Report
- 4 Customizing a Report
- Defining the Layout
- Specifying the Items in a Detail Line
- Naming Select List and Detail Line Items
- Organizing Rows Into Break Groups
- Labeling Information
- Formatting Data Values
- Formatting Dates and Times
- Using TACL to Pass Parameters
- Conditional Printing of Items or Line Entries
- Redefining Special Characters
- Calculating Totals
- Calculating Subtotals
- Printing Double-Byte Characters
- A Comparison of the Report Writer and the Enform Language
- Index
Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-14
Organizing Rows Into Break Groups
+> HEADING "Profit"
+> NAME PROFIT;
The following TOTAL command refers to the PROFIT column:
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 as
follows:
•
A group formed by a GROUP BY clause results in 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 of 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-15.
•
A break 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. The following example illustrates
a report defined with three levels of break groups. The commands that define the
report are:
>> SET LIST_COUNT 0;
>> VOLUME SALES;
>> 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,
+> QTY_ORDERED;
S> BREAK ON SALESREP, C.CUSTNUM, R.ORDERNUM;
S> LIST N 18;
Sales representatives form the major break group. The customers of each sales
representative form the next level break group. The orders from each customer form
the last break group. A title and footing can be printed at each level.