HP 3000 Manuals HP 3000 Manuals
Chapter 5 SORTING, SUBDIVIDING, SUMMARIZING
The discussion in Section 2 described how to use the Data Names Menu to
produce a simple report. That discussion skipped over several of the
prompts on the Data Names Menu. Now that the basic tasks of defining a
simple report, saving and producing it, and using the catalog have been
presented, it is time to look at the prompts that were passed over in
Section 2. Those prompts provide several powerful capabilities that
allow you to tailor your reports to more clearly answer you needs. The
three that are discussed in this section are sorting, subdividing, and
summarizing. The other prompts are discussed in Sections 6 through 9.
After you have selected the data names to be included in a report, the
next step is to decide whether you want data sorted, and, if so, what
data name to sort them on. For example, if you sort a sales report on
sales persons' names, then the data will appear in alphabetical order on
names. If you sort on sales district, then all the data for each sales
district will appear together, and the districts themselves will be in
numerical or alphabetical order.
If data for a report is sorted, it can also be subdivided. The data
names that are used for the sort are the only ones that can be used for
subdividing. Subdividing is simply a way to clarify the structure or
organization of the data in a report. For example, in a sales report
that is sorted on district, and then sorted on name within district, you
would probably want to subdivide on district, but not on name, because
there will be many records for each district, but only one for each name.
Subdividing causes INFORM/V to print only the first of a set of duplicate
values for a given data name. This simple device makes it very easy to
see when a new subdivision of data begins. For example, compare Figure
5-4 and Figure 5-6. Exactly the same data is presented in the two
figures, sorted in the same way, but only Figure 5-6 is subdivided.
If data in a report is subdivided, it can also be summarized. You can
get sub-summaries (subtotals, etc.) for subdivisions of data within the
report, or get grand summaries for all data in the report. The kinds of
summaries that are available are total, minimum, maximum, average, and
count.
This section describes the steps used to sort, subdivide, and summarize
data in reports.
Sorting
On the Data Names Menu, after you type the numbers for the data names you
want to appear in your report, the following prompt appears:
TO SORT BY>
You may type more than one number in response to this prompt. When the
report is produced, the first number listed is sorted first, the second
number second, and so on. In the example in Figure 5-1, the user sorts
on only one data name, SALES-PERSON. The resulting report is shown in
Figure 5-2.
________________________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986 |
| |
| DATA NAMES IN SALES |
| |
| 1: AREA 3: SALES-PERSON 5: YTD-SALES|
| 2: DIST 4: QUOTA |
| |
| TYPE NUMBER(S) FOR DATA NAME(S): |
| |
| TO INCLUDE IN REPORT> 1/5 |
| TO SORT BY> 3 |
| TO SUBDIVIDE BY> ! |
| |
| REPORT TITLE> |
| |
| PREPARING REPORT |
| |
| PRODUCE THE REPORT(Y/N)?> |
| |
| PRODUCE REPORT IN BATCH MODE?(N/Y)> |
| |
| |
________________________________________________________________________
Figure 5-1. Sorting On a Single Data Name
___________________________________________________________________________________
| |
| AREA DIST SALES-PERSON QUOTA YTD-SALES |
| ------------------------------------------------------------------------- |
| MW IOWA ALVAREZ 80000.00 38000.00|
| NW CAL BONNANO 145000.00 152000.00|
| MW IOWA CHU 85000.00 56000.00|
| MW MINN DRAGER 90000.00 61000.00|
| SW ARIZ EPHRAM 150000.00 123000.00|
| SW TEX FITZPATRICK 120000.00 84000.00|
| SW TEX GRODIN 140000.00 97000.00|
| NW WASH HOUDINI 130000.00 85000.00|
| NW CAL JONES 130000.00 87000.00|
| NW CAL KROEGER 170000.00 103000.00|
| NW OREG LAROUS 90000.00 50000.00|
| MW IOWA MARTINEZ 105000.00 77000.00|
| NW WASH NORTH 70000.00 45000.00|
| NW WASH OP 105000.00 100000.00|
| NW OREG PASTERNAK 65000.00 30000.00|
| MW MINN RADOSLAVICH 125000.00 89000.00|
| |
| ***** END OF REPORT ***** |
| |
| |
___________________________________________________________________________________
Figure 5-2. A Report Sorted on the Data Name SALES-PERSON
Data are sorted in ascending order by default. To specify descending
order, type a D after the number of the data name. In Figure 5-1, for
example, you would sort the values in SALES-PERSON in descending order by
typing 3D.
In Figure 5-3, the user sorts on three data names, AREA, DISTRICT, and
SALES-PERSON. The resulting report is shown in Figure 5-4.
________________________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986 |
| |
| DATA NAMES IN SALES |
| |
| 1: AREA 3: SALES-PERSON 5: YTD-SALES|
| 2: DIST 4: QUOTA |
| |
| TYPE NUMBER(S) FOR DATA NAME(S): |
| |
| TO INCLUDE IN REPORT> 1/5 |
| TO SORT BY> 1,2,3 |
| TO SUBDIVIDE BY> ! |
| |
| REPORT TITLE> |
| |
| PREPARING REPORT |
| |
| PRODUCE THE REPORT(Y/N)?> |
| |
| PRODUCE REPORT IN BATCH MODE?(N/Y)> |
________________________________________________________________________
Figure 5-3. Sorting Data on Three Data Names
When you sort on more than one data name, the second is sorted within the
scope of the first, the third within the second, etc. For example, in
Figure 5-3, there are many sales people in each district, and there are
many districts in each area. Therefore the user sorts area first,
district second, and sales person third. If the user specified 3,2,1
instead of 1,2,3 for the sort, a simple alphabetical sort by sales person
would be obtained, since there is only one district and one area for each
sales person.
___________________________________________________________________________________
| |
| AREA DIST SALES-PERSON QUOTA YTD-SALES |
| --------------------------------------------------------------------------- |
| MW IOWA ALVAREZ 80000.00 38000.00|
| MW IOWA CHU 85000.00 56000.00|
| MW IOWA MARTINEZ 105000.00 77000.00|
| MW MINN DRAGER 90000.00 61000.00|
| MW MINN RADOSLAVICH 125000.00 89000.00|
| NW CAL BONNANO 145000.00 152000.00|
| NW CAL JONES 130000.00 87000.00|
| NW CAL KROEGER 170000.00 103000.00|
| NW OREG LAROUS 90000.00 50000.00|
| NW OREG PASTERNAK 65000.00 30000.00|
| NW WASH HOUDINI 130000.00 85000.00|
| NW WASH NORTH 70000.00 45000.00|
| NW WASH OP 105000.00 100000.00|
| SW ARIZ EPHRAM 150000.00 123000.00|
| SW TEX FITZPATRICK 120000.00 84000.00|
| SW TEX GRODIN 140000.00 97000.00|
| |
| ***** END OF REPORT ***** |
| |
| |
___________________________________________________________________________________
Figure 5-4. A Report Sorted On Three Data Names
If you do not need to sort the data, respond to the SORT prompt with a
RETURN. The prompts for subdividing and summarizing will then not appear,
since there is no data name on which to subdivide.
Subdividing
If you responded to the SORT prompt by entering one or more numbers, you
then have the opportunity to subdivide the data. The prompt is:
TO SUBDIVIDE BY>
If you want to emphasize the organization of the sorted information,
respond to this prompt by typing one (or more) of the numbers you
specified for sorting. INFORM/V will then print the value for a
subdivision data name only when it changes, that is, only when a new
subdivision starts.
In Figure 5-5, the user wants to subdivide on AREA and DISTRICT.
________________________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986 |
| |
| DATA NAMES IN SALES |
| |
| 1: AREA 3: SALES-PERSON 5: YTD-SALES|
| 2: DIST 4: QUOTA |
| |
| TYPE NUMBER(S) FOR DATA NAME(S): |
| |
| TO INCLUDE IN REPORT> 1/5 |
| TO SORT BY> 1,2,3 |
| TO SUBDIVIDE BY> 1,2 |
| TO SUMMARIZE FOR: AREA> ! |
| |
| REPORT TITLE> |
| |
| PREPARING REPORT |
| |
| PRODUCE THE REPORT(Y/N)?> |
| |
| PRODUCE REPORT IN BATCH MODE?(N/Y)> |
| |
| |
________________________________________________________________________
Figure 5-5. Subdividing on Two Data Names
The resulting report is shown in Figure 5-6. If you compare Figure 5-6
with Figure 5-4, you can quickly see how much subdividing improves the
readability of the report.
____________________________________________________________________________________
| |
| AREA DIST SALES-PERSON QUOTA YTD-SALES |
| -----------------------------------------------------------------------------|
| MW IOWA ALVAREZ 80000.00 38000.00 |
| CHU 85000.00 56000.00 |
| MARTINEZ 105000.00 77000.00 |
| MINN DRAGER 90000.00 61000.00 |
| RADOSLAVICH 125000.00 89000.00 |
| NW CAL BONNANO 145000.00 152000.00 |
| JONES 130000.00 87000.00 |
| KROEGER 170000.00 103000.00 |
| OREG LAROUS 90000.00 50000.00 |
| PASTERNAK 65000.00 30000.00 |
| WASH HOUDINI 130000.00 85000.00 |
| NORTH 70000.00 45000.00 |
| OP 105000.00 100000.00 |
| SW ARIZ EPHRAM 150000.00 123000.00 |
| TEX FITZPATRICK 120000.00 84000.00 |
| GRODIN 140000.00 97000.00 |
| |
| ***** END OF REPORT ***** |
| |
| |
____________________________________________________________________________________
Figure 5-6. A Report Subdivided on Two Data Names
Summarizing
The next step in creating a report is to decide whether you want to
summarize any data at the subdivision level. INFORM/V offers five
arithmetic functions, shown in Table 5-1, that allow you to get summary
information without having to do any programming. You simply enter the
number of the data item that you want summarized, along with the name or
abbreviation of one of the five functions. For example, 6AVG means that
you want to see the average of values for data name 6 for each
subdivision of the data.
It is also possible to summarize data for the whole report. The "Report
Summary" capability is discussed later in this section.
The prompt for summarizing data at the subdivision level is
TO SUMMARIZE FOR: dataname>
INFORM/V repeats the SUMMARIZE prompt for each data name on which you
asked for subdividing. For each subdivision for which you want summary
data, type the number corresponding to the data name, followed
immediately by the function. If you want a summary on more than one data
name at a given subdivision, separate the number-function pairs by
commas. For example,
1COUNT,2AVERAGE,5TOTAL
Table 5-1. Arithmetic Functions
-------------------------------------------------------------------------------------------------
| Function | Purpose | Specification | Example of Output |
-------------------------------------------------------------------------------------------------
| AVERAGE | Averages values for | 6AVERAGE | 264A |
| | the data name | 6avg | |
| | | 6A | |
-------------------------------------------------------------------------------------------------
| COUNT | Tallies the number of | 7COUNT | 43C |
| | entries of a | 7c | |
| | particular data name | | |
-------------------------------------------------------------------------------------------------
| MAXIMUM | Finds the highest | 8MAXIMUM | 1120> |
| | value of a data name | 8max | |
| | | 8MA | |
-------------------------------------------------------------------------------------------------
| MINIMUM | Finds the lowest | 4minimum | 13< |
| | value of a data name | 4MIN | |
| | | 4MI | |
-------------------------------------------------------------------------------------------------
| TOTAL | Adds all values of a | 9total | 9961* |
| | data name; when | | |
| | used with sorted | 9T | |
| | data, the value is | | |
| | a subtotal; when used | 9 | |
| | with report summary, | | |
| | the value is a grand | | |
| | total | | |
-------------------------------------------------------------------------------------------------
You may use only one arithmetic function per data name. For example, if
you typed the following:
11AVERAGE,11MINIMUM
INFORM/V would reject this entry and prompt for the summary information
again.
In the example in Figure 5-7, the user is requesting totals on data name
5, YTD-SALES, for each area and for each district; that is, for each
subdivision of the report.
________________________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986 |
| |
| DATA NAMES IN SALES |
| |
| 1: AREA 3: SALES-PERSON 5: YTD-SALES|
| 2: DIST 4: QUOTA |
| |
| TYPE NUMBER(S) FOR DATA NAME(S): |
| |
| TO INCLUDE IN REPORT> 1/5 |
| TO SORT BY> 1,2,3 |
| TO SUBDIVIDE BY> 1,2 |
| TO SUMMARIZE FOR: AREA> 5TOTAL |
| TO SUMMARIZE FOR: DIST> 5TOTAL |
| FOR REPORT SUMMARY> ! |
| |
| REPORT TITLE> |
| |
| PREPARING REPORT |
| |
| PRODUCE THE REPORT(Y/N)?> |
| |
| PRODUCE REPORT IN BATCH MODE?(N/Y)> |
| |
| |
________________________________________________________________________
Figure 5-7. Requesting a Report with Sorts, Subdivisions, and Summaries
The specifications given in Figure 5-7 produce the report shown in Figure
5-8. Summary data - - in this case subtotals - - are computed each time
AREA and DISTRICT change. The totals are marked with an arrow (=>) and
an asterisk (*) to the right of the value.
____________________________________________________________________________________
| |
| AREA DIST SALES-PERSON QUOTA YTD-SALES |
| -----------------------------------------------------------------------------|
| MW IOWA ALVAREZ 80000.00 38000.00 |
| CHU 85000.00 56000.00 |
| MARTINEZ 105000.00 77000.00 |
| => 171000.00*|
| |
| MINN DRAGER 90000.00 61000.00 |
| RADOSLAVICH 125000.00 89000.00 |
| => 150000.00*|
| 321000.00*|
| |
| NW CAL BONNANO 145000.00 152000.00 |
| JONES 130000.00 87000.00 |
| KROEGER 170000.00 103000.00 |
| => 342000.00*|
| |
| OREG LAROUS 90000.00 50000.00 |
| PASTERNAK 65000.00 30000.00 |
| => 80000.00*|
| |
| WASH HOUDINI 130000.00 85000.00 |
| |
| CONTINUE(Y/N)> |
| |
| |
____________________________________________________________________________________
Figure 5-8. A Report with Summaries for Two Subdivisions
____________________________________________________________________________________
| |
| AREA DIST SALES-PERSON QUOTA YTD-SALES |
| -----------------------------------------------------------------------------|
| NW WASH NORTH 70000.00 45000.00 |
| OP 105000.00 100000.00 |
| => 230000.00*|
| 652000.00*|
| |
| SW ARIZ EPHRAM 150000.00 123000.00 |
| => 123000.00*|
| |
| TEX FITZPATRICK 120000.00 84000.00 |
| GRODIN 140000.00 97000.00 |
| => 181000.00*|
| 304000.00*|
| |
| |
| ***** END OF REPORT ***** |
| |
| |
____________________________________________________________________________________
Figure 5-8. A Report with Summaries for Two Subdivisions (continued)
Report Summary
In addition to summarizing data for each subdivision, you can summarize
data for the entire report by responding to the prompt:
FOR REPORT SUMMARY>
The procedure used to summarize report data is identical to that used to
summarize subdivided data. For example, if you want a grand total of all
year-to-date sales and a count of all sales people, respond as shown in
Figure 5-9. You would type the following:
FOR REPORT SUMMARY> 5TOTAL,3COUNT
(The prompt
FOR SELECTION CRITERIA>
is described in Section 6.)
________________________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986 |
| |
| DATA NAMES IN SALES |
| |
| 1: AREA 3: SALES-PERSON 5: YTD-SALES|
| 2: DIST 4: QUOTA |
| |
| TYPE NUMBER(S) FOR DATA NAME(S): |
| |
| TO INCLUDE IN REPORT> 1/5 |
| TO SORT BY> 1,2,3 |
| TO SUBDIVIDE BY> 1,2 |
| TO SUMMARIZE FOR: AREA> 5TOTAL |
| TO SUMMARIZE FOR: DIST> 5TOTAL |
| FOR REPORT SUMMARY> 5TOTAL,3COUNT |
| FOR SELECTION CRITERIA> |
| |
| REPORT TITLE> |
| |
| PREPARING REPORT |
| |
| PRODUCE THE REPORT(Y/N)?> |
| |
| PRODUCE REPORT IN BATCH MODE?(N/Y)> |
| |
| |
________________________________________________________________________
Figure 5-9. Specifying a Report Summary on the Data Names Menu
When you summarize data for the whole report, the process is independent
of any sorting or subdividing done for the report. You can apply any of
the five arithmetic functions to any data name that makes sense. For
example, the COUNT function can be applied to any data name, and the
other four functions can be applied to any data name on which arithmetic
is appropriate.
Figure 5-10 shows the report produced by the interaction in Figure 5-9.
The total number of values for the data name SALES-PERSON in this report
(3COUNT) is shown at the end of the report below the last entry for
SALES-PERSON. The designation 16C means that for this report, the total
count of names is 16. The grand total of year-to-date sales for the
whole report is shown on the same line. The designation * tells you that
the value is a total.
____________________________________________________________________________________
| |
| AREA DIST SALES-PERSON QUOTA YTD-SALES |
| -----------------------------------------------------------------------------|
| MW IOWA ALVAREZ 80000.00 38000.00 |
| CHU 85000.00 56000.00 |
| MARTINEZ 105000.00 77000.00 |
| => 171000.00* |
| |
| MINN DRAGER 90000.00 61000.00 |
| RADOSLAVICH 125000.00 89000.00 |
| => 150000.00* |
| 321000.00*|
| |
| NW CAL BONNANO 145000.00 152000.00 |
| JONES 130000.00 87000.00 |
| KROEGER 170000.00 103000.00 |
| => 342000.00* |
| |
| OREG LAROUS 90000.00 50000.00 |
| PASTERNAK 65000.00 30000.00 |
| => 80000.00* |
| |
| WASH HOUDINI 130000.00 85000.00 |
| |
| CONTINUE(Y/N)> |
| |
| |
____________________________________________________________________________________
Figure 5-10. Data Summarized for Whole Report
____________________________________________________________________________________
| |
| AREA DIST SALES-PERSON QUOTA YTD-SALES |
| -----------------------------------------------------------------------------|
| NW WASH NORTH 70000.00 45000.00 |
| OP 105000.00 100000.00 |
| => 230000.00*|
| 652000.00*|
| |
| SW ARIZ EPHRAM 150000.00 123000.00 |
| => 123000.00*|
| |
| TEX FITZPATRICK 120000.00 84000.00 |
| GRODIN 140000.00 97000.00 |
| => 181000.00*|
| 304000.00*|
| 16C 1277000.00*|
| |
| |
| ***** END OF REPORT ***** |
| |
| |
____________________________________________________________________________________
Figure 5-10. Data Summarized for Whole Report (continued)
Summary-Only Reports. The first prompt on the Data Names Menu is
TO INCLUDE IN REPORT>
Up to this point, all examples of how to define reports have shown the
user responding by entering one or more numbers, representing the data
names to be included in the report.
It is also possible to get just summary information in a report, without
listing the individual data items. If you do not list any numbers in
response to this prompt, then INFORM/V produces a summary-only report.
The way in which you sort and subdivide determines the points at which
you will get summary data, as the following example shows.
Suppose a user has a personnel information database and wants to know how
many employees are in each division. If each employee in each division
is listed in the report, it will be very long and the summary information
(which is all the user wants) will be hard to find (see Figure 5-11).
__________________________________________
| |
| EMPLOYEE COUNT AT EACH DIVISION |
| |
| DIVISION EMPLOYEE |
| -----------------------------------|
| ABC ACTON, JOE |
| ANDERSON, BARBARA |
| . |
| . |
| . |
| ZINK, STEVEN |
| 235C |
| |
| RST ARRON, JUDY |
| ARRON, LEWIS |
| . |
| . |
| . |
| ZORN, JACK |
| 127C |
| |
__________________________________________
Figure 5-11. Employee Report with Summary Counts
For a large database, this would be a very long report with extraneous
information that is of no particular interest. It would be preferable to
define the report as shown in Figure 5-12.
_____________________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986 |
| |
| DATA NAMES IN PERSONNEL INFORMATION |
| |
| 1: DIVISION 4: EMPLOYEE NUMBER 7: PHONE EXT |
| 2: DIVISION NUMBER 5: MANAGER 8: MAIL STOP #|
| 3: EMPLOYEE 6: DATE HIRED |
| |
| TYPE NUMBER(S) FOR DATA NAME(S): |
| TO INCLUDE IN REPORT> |
| TO SORT BY> 1 |
| TO SUBDIVIDE BY> 1 |
| TO SUMMARIZE FOR: DIVISION> 3COUNT |
| |
_____________________________________________________________________
Figure 5-12. Defining a Summary-Only Report
The report produced by the example in Figure 5-12 is shown in Figure
5-13.
_______________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986|
| |
| EMPLOYEE COUNT AT EACH DIVISION |
| |
| DIVISION EMPLOYEE |
| ---------------------- |
| ABC 235C |
| RST 127C |
| . . |
| . |
| . . |
| |
| XYZ 303C |
| |
| |
_______________________________________________________________
Figure 5-13. A Summary-Only Report