Specifying the Sort Order

In this section:

How to:

Sort field values are automatically displayed in ascending order, beginning with the lowest value and continuing to the highest. The default sorting sequence varies for operating systems. On z/OS it is a-z, A-Z, 0-9 for alphanumeric fields; 0-9 for numeric fields. On UNIX and Windows it is 0-9, A-Z, a-z for alphanumeric fields; 0-9 for numeric fields.

You have the option of overriding this default and displaying values in descending order, ranging from the highest value to the lowest value, by including HIGHEST in the sort phrase.


Top of page

x
Syntax: How to Specify the Sort Order
{BY|ACROSS} {LOWEST|HIGHEST} sortfield					

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). This option is the default.

HIGHEST

Sorts in descending order, beginning with the highest value and continuing to the lowest value. You can also use TOP as a synonym for HIGHEST.

sortfield

Is the name of the sort field.



Example: Sorting in Ascending Order

The following report request does not specify a particular sorting order, and so, by default, it lists salaries ranging from the lowest to the highest.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY CURR_SAL
END

You can specify this same ascending order explicitly by including LOWEST in the sort phrase.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY LOWEST CURR_SAL
END

The output is:



Example: Sorting in Descending Order

The following request lists salaries ranging from the highest to lowest.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY HIGHEST CURR_SAL
END

The output is:


Top of page

x
Specifying Your Own Sort Order

In this section:

How to:

Reference:

Sort field values are automatically displayed in ascending order, beginning with the lowest value and continuing to the highest.

You can override the default order and display values in your own user-defined sorting sequence. To do this, you need to decide the following:

  1. Which sort field values you want to allow. You can specify every sort field value, or a subset of values. When you issue your report request, only records containing those values are included in the report.
  2. The order in which you want the values to appear. You can specify any order. For example, you could specify that an A1 sort field containing a single-letter code be sorted in the order A, Z, B, C, Y, and so on.

There are two ways to specify your own sorting order, depending on whether you are sorting rows with BY, or sorting columns with ACROSS:



x
Syntax: How to Define Your Own Sort Order
BY sortfield AS 'coltitle' ROWS value1 [AS 'text1'] 
OVER value2 [AS 'text2']
[... OVER valuen [ AS 'textn']]
END

where:

sortfield

Is the last BY field in the report.

coltitle

Is the column title for the BY field on the report output.

value1

Is the sort field value that is first in the sorting sequence.

AS 'text1'

Enables you to assign alternate text for the first row, which replaces the field value in the output. Enclose the text in single quotation marks.

value2

Is the sort field value that is second in the sorting sequence.

AS 'text2'

Enables you to assign alternate text for the second row, which replaces the field value in the output. Enclose the text in single quotation marks.

valuen

Is the sort field value that is last in the sorting sequence.

AS 'textn'

Enables you to assign alternate text for the last row, which replaces the field value in the output. Enclose the text in single quotation marks.

An alternative syntax is

FOR sortfield
value1 OVER value2 [... OVER valuen]

which uses the row-based reporting phrase FOR, described in Creating Financial Reports With Financial Modeling Language (FML).



x
Reference: Usage Notes for Defining Your Sort Order


Example: Defining Your Row Sort Order

The following illustrates how to sort employees by the banks at which their paychecks are automatically deposited, and how to define your own label in the sorting sequence for the bank field.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY BANK_NAME ROWS 'BEST BANK' OVER STATE
   OVER ASSOCIATED OVER 'BANK ASSOCIATION'
END

The output is:



x
Syntax: How to Define Column Sort Sequence
ACROSS sortfield COLUMNS value1 AND value2 [... AND valuen]

where:

sortfield

Is the name of the sort field.

value1

Is the sort field value that is first in the sorting sequence.

value2

Is the sort field value that is second in the sorting sequence.

valuen

Is the sort field value that is last in the sorting sequence.



x
Reference: Usage Notes for Defining Column Sort Sequence


Example: Defining Column Sort Sequence

The following illustrates how to sum employee salaries by the bank at which they are automatically deposited, and to define your own label within the sorting sequence for the bank field.

TABLE FILE EMPLOYEE
SUM CURR_SAL
ACROSS BANK_NAME COLUMNS 'BEST BANK' AND STATE
   AND ASSOCIATED AND 'BANK ASSOCIATION'
END

The output is:

BANK_NAME                                                                    
BEST BANK          STATE              ASSOCIATED         BANK ASSOCIATION
-------------------------------------------------------------------------
     $29,700.00         $18,480.00         $64,742.00         $27,062.00


x
Selecting and Assigning Column Titles to ACROSS Values

When you use the ACROSS COLUMNS phrase to select and order the columns that display on the report output for an ACROSS sort field, you can assign each selected column a new column title using an AS phrase.



x
Syntax: How to Assign Column Titles To ACROSS Values
ACROSS sortfield [AS title]
 COLUMNS aval1 [AS val1title] [{AND|OR} aval2 [AS val2title] [... {AND|OR} avaln [AS valntitle]]]

where:

sortfield

Is the ACROSS field name.

title

Is the title for the ACROSS field name.

AND|OR

Is required to separate the selected ACROSS values. AND and OR are synonyms for this purpose.

aval1, aval2, ... avaln

Are the selected ACROSS values to display on the report output.

val1title, val2title ...valntitle

Are the column titles for the selected ACROSS values.



x
Reference: Usage Notes for Assigning Column Titles to ACROSS Values
  • Any value you specify as an ACROSS value in the sort phrase will appear on the report output, even if the value is screened out by an IF or WHERE test, or if the value does not exist at all in the data source.

    Note: For styled output formats, SET HIDENULLACRS=ON removes empty columns in ACROSS groups from the report output.

  • Column titles for ACROSS fields appear on a single line of the report output.
  • Support for AS names for ACROSS values is limited to the TABLE FILE command.
  • When you create a HOLD file with SET ASNAMES = ON, the original field name is propagated to the output Master File, not the AS name.


Example: Selecting and Assigning Column Titles to ACROSS Values

The following request against the GGSALES data source selects the columns Coffee Grinder, Latte, and Coffee Pot for the ACROSS field PRODUCT, and assigns each of them a new column title:

 TABLE FILE GGSALES                     
 SUM                                    
 DOLLARS/I8M AS ''                      
 BY REGION                              
 ACROSS PRODUCT  AS 'Products'          
   COLUMNS 'Coffee Grinder' AS 'Grinder'
    OR Latte AS 'caffellatte'              
    AND 'Coffee Pot' AS 'Carafe'           
ON TABLE SET PAGE NOPAGE
END                                    

The output is:

             Products                                           
             Grinder          caffellatte      Carafe           
Region                                                          
----------------------------------------------------------------
Midwest         $666,622       $2,883,566         $599,878      
Northeast       $509,200       $2,808,855         $590,780      
Southeast       $656,957       $2,637,562         $645,303      
West            $603,436       $2,670,405         $613,624                                                                   

WebFOCUS