Sorting Rows

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.


Top of page

x
Syntax: How to Sort by Rows
BY sortfield

where:

sortfield

Is the name of the sort field.


Top of page

x
Reference: Usage Notes for Sorting Rows


Example: Sorting Rows With BY

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:

DEPARTMENT  EMP_ID   
----------  ------   
MIS         112847612
            117593129
            219984371
            326179357
            543729165
            818692173
PRODUCTION  071382660
            119265415
            119329144
            123764317
            126724188
            451123478

Top of page

x
Displaying All Vertical (BY) Sort Field Values

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


x
Syntax: How to Display All Vertical (BY) Sort Field Values
SET BYDISPLAY = {OFF|ON}

or

ON TABLE SET BYDISPLAY {OFF|ON}

where:

OFF

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.

ON

Displays the associated BY field value on every line of report output produced in a styled format.



Example: Displaying All Vertical (BY) Sort Field Values

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 HOLD FORMAT EXL2K 
END

The output is:


Top of page

x
Using Multiple Vertical (BY) Sort Fields

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.



Example: Sorting With Multiple Vertical (BY) Sort Fields

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

Top of page

x
Displaying a Row for Data Excluded by a Sort Phrase

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.



x
Syntax: How to Display Data Excluded by a Sort Phrase
[RANKED] BY {HIGHEST|LOWEST|TOP|BOTTOM} nsrtfield [AS 'text'] 
            [PLUS OTHERS AS 'othertext'] 
            [IN-GROUPS-OF m1 [TOP n2]] 
            [IN-RANGES-OF m3 [TOP n4]

where:

LOWEST

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.

HIGHEST

Sorts in descending order, beginning with the highest value and continuing to the lowest value. TOP is a synonym for HIGHEST.

n

Specifies that only n sort field values are included in the report.

srtfield

Is the name of the sort field.

text

Is the text to be used as the column heading for the sort field values.

othertext

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.

m1

Is the incremental value between sort field groups.

n2

Is an optional number that defines the highest group label to be included in the report.

m3

Is an integer greater than zero indicating the range by which sort field values are grouped.

n4

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.



x
Reference: Usage Notes for PLUS OTHERS


Example: Displaying a Row Representing Sort Field Values Excluded by a Sort Phrase

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


Example: Displaying a Row Representing Data Not Included in Any Sort Field Grouping

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

Information Builders