In this section:
How to: Reference: |
You can sort report information vertically using the BY phrase. This creates rows in your report. You can include up to 128 sort phrases (BY phrases plus ACROSS phrases) per report request (127 if using PRINT or LIST display commands).
Sort fields appear when their value changes. However, you can display every sort value using the BYDISPLAY parameter. For an example, see Displaying All Vertical (BY) Sort Field Values.
BY {HIGHEST|LOWEST} [n] sortfield [AS 'text']
where:
Sorts in descending order.
Sorts in ascending order. LOWEST is the default value.
Specifies that only n sort field values are included in the report.
Is the name of the sort field.
Is the column heading to use for the sort field column on the report output.
PRINT LAST_NAME BY DEPARTMENT
prints MIS once, followed by six employee names. You can populate every vertical sort column cell with a value, even if the value is repeating, using the SET BYDISPLAY parameter. For details, see Displaying All Vertical (BY) Sort Field Values.
The following illustrates how to display all employee IDs by department.
TABLE FILE EMPLOYEE PRINT EMP_ID BY DEPARTMENT END
The output displays a row for each EMP_ID in each department:
How to: |
Within a vertical sort group, the sort field value displays only on the first line of the rows for its sort group, and on the first line of a page. However, using the SET BYDISPLAY command, you can display the appropriate BY field on every row of a report produced in a styled output format.
Although SET BYDISPLAY is supported for all styled output formats, it is especially important for making report output more usable by Excel, which cannot sort columns properly when they have blank values in some rows.
This feature enables you to avoid specifying the sort field twice, once as a display field and once for sorting (with the NOPRINT option). For example:
PRINT FIRST_NAME LAST_NAME BY FIRST_NAME NOPRINT
SET BYDISPLAY = {OFF|ON}
or
ON TABLE SET BYDISPLAY {OFF|ON}
where:
Displays a BY field value only on the first line of the report output for the sort group and on the first line of a page. OFF is the default value.
Displays the associated BY field value on every line of report output produced in a styled format.
The following illustrates how you can display every instance of a vertical (BY) sort field value in a styled report using SET BYDISPLAY.
SET BYDISPLAY = ON
TABLE FILE CENTHR
PRINT LNAME
BY FNAME
WHERE FNAME EQ 'CAROLYN' OR 'DAVID' ON
TABLE PCHOLD FORMAT EXL2KEND
The output is:
You can organize information in a report by using more than one sort field. When you specify several sort fields, the sequence of the BY phrases determines the sort order. The first BY phrase sets the major sort break, the second BY phrase sets the second sort break, and so on. Each successive sort is nested within the previous one.
The following request uses multiple vertical (BY) sort fields.
TABLE FILE EMPLOYEE PRINT CURR_SAL BY DEPARTMENT BY LAST_NAME WHERE CURR_SAL GT 21500 END
The output is:
DEPARTMENT LAST_NAME CURR_SAL ---------- --------- -------- MIS BLACKWOOD $21,780.00 CROSS $27,062.00 PRODUCTION BANNING $29,700.00 IRVING $26,862.00
How to: Reference: |
In a sort phrase, you can restrict the number of sort values displayed. With the PLUS OTHERS phrase, you can aggregate all other values to a separate group and display this group as an additional report row.
[RANKED] BY {HIGHEST|LOWEST|TOP|BOTTOM} n srtfield [AS 'text'] [PLUS OTHERS AS 'othertext'] [IN-GROUPS-OF m1 [TOP n2]] [IN-RANGES-OF m3 [TOP n4]
where:
Sorts in ascending order, beginning with the lowest value and continuing to the highest value (a-z, A-Z, 0-9 for alphanumeric fields; 0-9 for numeric fields). BOTTOM is a synonym for LOWEST.
Sorts in descending order, beginning with the highest value and continuing to the lowest value. TOP is a synonym for HIGHEST.
Specifies that only n sort field values are included in the report.
Is the name of the sort field.
Is the text to be used as the column heading for the sort field values.
Is the text to be used as the row title for the "others" grouping. This AS phrase must be the AS phrase immediately following the PLUS OTHERS phrase.
Is the incremental value between sort field groups.
Is an optional number that defines the highest group label to be included in the report.
Is an integer greater than zero indicating the range by which sort field values are grouped.
Is an optional number that defines the highest range label to be included in the report. The range is extended to include all data values higher than this value.
The following request displays the top two ED_HRS values and aggregates the values not included in a row labeled Others:
TABLE FILE EMPLOYEE PRINT CURR_SAL LAST_NAME BY HIGHEST 2 ED_HRS PLUS OTHERS AS 'Others' END
The output is:
ED_HRS CURR_SAL LAST_NAME ------ -------- --------- 75.00 $21,780.00 BLACKWOOD 50.00 $18,480.00 JONES $16,100.00 MCKNIGHT Others $165,924.00
The following request sorts by highest 2 ED_HRS and groups the sort field values by increments of 25 ED_HRS. Values that fall below the lowest group label are included in the Others category. All values above the top group label are included in the top group:
TABLE FILE EMPLOYEE PRINT CURR_SAL LAST_NAME BY HIGHEST 2 ED_HRS PLUS OTHERS AS 'Others' IN-GROUPS-OF 25 TOP 50 END
The output is:
ED_HRS CURR_SAL LAST_NAME ------ -------- --------- 50.00 $18,480.00 JONES $21,780.00 BLACKWOOD $16,100.00 MCKNIGHT 25.00 $11,000.00 STEVENS $13,200.00 SMITH $26,862.00 IRVING $9,000.00 GREENSPAN $27,062.00 CROSS Others $78,800.00
If the BY HIGHEST phrase is changed to BY LOWEST, all values above the top grouping (50 ED_HRS and above) are included in the Others category:
TABLE FILE EMPLOYEE PRINT CURR_SAL LAST_NAME BY LOWEST 2 ED_HRS PLUS OTHERS AS 'Others' IN-GROUPS-OF 25 TOP 50 END
The output is:
ED_HRS CURR_SAL LAST_NAME ------ -------- --------- .00 $9,500.00 SMITH $29,700.00 BANNING $21,120.00 ROMANS $18,480.00 MCCOY 25.00 $11,000.00 STEVENS $13,200.00 SMITH $26,862.00 IRVING $9,000.00 GREENSPAN $27,062.00 CROSS Others $56,360.00
WebFOCUS |