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.
{BY|ACROSS} {LOWEST|HIGHEST} sortfield
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). This option is the default.
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.
Is the name of the sort field.
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:
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:
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:
There are two ways to specify your own sorting order, depending on whether you are sorting rows with BY, or sorting columns with ACROSS:
BY sortfield AS 'coltitle' ROWS value1 [AS 'text1'] OVER value2 [AS 'text2'] [... OVER valuen [ AS 'textn']] END
where:
Is the last BY field in the report.
Is the column title for the BY field on the report output.
Is the sort field value that is first in the sorting sequence.
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.
Is the sort field value that is second in the sorting sequence.
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.
Is the sort field value that is last in the sorting sequence.
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).
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:
ACROSS sortfield COLUMNS value1 AND value2 [... AND valuen]
where:
Is the name of the sort field.
Is the sort field value that is first in the sorting sequence.
Is the sort field value that is second in the sorting sequence.
Is the sort field value that is last in the sorting sequence.
ACROSS acrossfield [AND] COMPUTE compute_expression; COLUMNS values
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
WebFOCUS |