Specifying the Sort Order

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:

  CURR_SAL  LAST_NAME
  --------  ---------
 $9,000.00  GREENSPAN
 $9,500.00  SMITH    
$11,000.00  STEVENS  
$13,200.00  SMITH    
$16,100.00  MCKNIGHT 
$18,480.00  JONES    
            MCCOY    
$21,120.00  ROMANS   
$21,780.00  BLACKWOOD
$26,862.00  IRVING   
$27,062.00  CROSS    
$29,700.00  BANNING

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:

  CURR_SAL  LAST_NAME
  --------  ---------
$29,700.00  BANNING  
$27,062.00  CROSS    
$26,862.00  IRVING   
$21,780.00  BLACKWOOD
$21,120.00  ROMANS   
$18,480.00  JONES    
            MCCOY    
$16,100.00  MCKNIGHT 
$13,200.00  SMITH    
$11,000.00  STEVENS  
 $9,500.00  SMITH    
 $9,000.00  GREENSPAN

Specifying Your Own Sort Order

In this section:

Selecting and Assigning Column Titles to ACROSS Values

How to:

Define Your Own Sort Order
Define Column Sort Sequence

Reference:

Usage Notes for Defining Your Sort Order
Usage Notes for Defining Column Sort Sequence

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:

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.
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:

The BY ROWS OVER phrase, for defining your own row sort sequence.
The ACROSS COLUMNS AND phrase, for defining your own column sort sequence.

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).

Reference: Usage Notes for Defining Your Sort Order

Any sort field value that you do not specify in the BY ROWS OVER phrase is not included in the sorting sequence, and does not appear in the report.
Sort field values that contain embedded blank spaces should be enclosed in single quotation marks (').
Any sort field value that you do specify in the BY ROWS OVER phrase is included in the report, whether or not there is data.
If missing data is included in the report, it must be inserted at the lowest sort level.
The name of the sort field is not included in the report.
Each report request can contain only one BY ROWS OVER phrase. BY ROWS OVER is not supported with the FOR phrase. For information about the FOR phrase, see Creating Financial Reports With Financial Modeling Language (FML).

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:

                  LAST_NAME
                  ---------
BEST BANK         BANNING  
STATE             JONES    
ASSOCIATED        IRVING   
ASSOCIATED        BLACKWOOD
ASSOCIATED        MCKNIGHT 
BANK ASSOCIATION  CROSS

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.

Reference: Usage Notes for Defining Column Sort Sequence

Any sort field value that you do not specify in the ACROSS COLUMNS AND phrase is not included in the label within the sorting sequence, and does not appear in the report.
Sort field values that contain embedded blank spaces should be enclosed in single quotation marks.
Any sort field value that you do specify in the ACROSS COLUMNS AND phrase is included in the report, whether or not there is data.
When using a COMPUTE with an ACROSS COLUMNS phrase, the COLUMNS should be specified last:
```
ACROSS acrossfield [AND] COMPUTE compute_expression; COLUMNS values
```
Each report request may contain only one BY ROWS OVER phrase.

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

Selecting and Assigning Column Titles to ACROSS Values

How to:

Assign Column Titles To ACROSS Values

Reference:

Usage Notes for 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.

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.

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