IBM SC41-5210-04 Types of summary functions in Query for iSeries reports, =Total, Max, Count

Chapter 11. Specifying report summary functions in Query for iSeries reports

This chapter describes how to specify the summary functions for each of the selected fields in your query output. Depending on the type of field, you can specify one or more (or all) of the types of summary functions for each field in your report: total, average, minimum value, maximum value, and count. All of them can be used for numeric fields, and all except total and average can be used for character (SBCS and DBCS), date, time, and timestamp fields.

For each field for which you specify summary functions, Query calculates summary values and includes them in the report. Query calculates these summary values at each report break (break levels 1 through 6) defined in this query and at the end of the report (break level 0). Each type of summary is shown on a separate line in the report, with a descriptive abbreviation shown on the left of the summary values (see Chapter 12, “Defining Query for iSeries report breaks”).

For example, assume that your query has a numeric result field named ITEMTOT defined using the expression QUANTITY * ITEMCOST (two numeric fields being multiplied together). These two fields are used to calculate the cost of each item ordered in the ITEM field. You might define the following summary functions for those fields: count for the ITEM field, total and maximum for the QUANTITY field, maximum for the ITEMCOST field, and total and maximum for the ITEMTOT field. The following is an example of how part of a report might look for a customer named Z Z Smith:

ITEM	QUANTITY	ITEMCOST	ITEMTOT
Bolt	12	.10	1.20
Hammer	2	8.50	17.00
Ruler	1	2.00	2.00
Screw	6	.05	.30
Totals for: Z Z Smith
TOTAL	21		20.50
MAX	12	8.50	17.00
COUNT 4

Each summary function result for each field (column) is calculated and included as a summary value (similar to a subtotal) at every defined report break and as a final summary (final total) at the end of the report. (However, using the Define Report Break display, you can format a particular break level to suppress the summary information. In this case, when a break occurs, a blank line and possibly break text that includes break values, if defined, is used.)

Summary function results, if defined, can appear in all three types (displayed, printed, and database) and both forms (detailed and summary) of output, with one exception: they cannot be included if the report is to a database file in detailed form.

Types of summary functions in Query for iSeries reports

On the Select Report Summary Functions display, you can enter the number of one or more of the following options to specify the types of summary functions to be used for the fields. You can specify as many options for as many fields as you want, if they are valid for that type of field. If you type more than one option for a field, they can be typed in any order. The order that the summaries appear in the report, however, is the same as listed here, and cannot be changed:

1=Total

Shows the sum of the values in the field for the break level or for the whole column (numeric fields only). Null values are ignored unless all values are null, then the total is null.

© Copyright IBM Corp. 2000, 2002 135