How to: |
Using the BY TOTAL phrase, you can apply aggregation and sorting simultaneously to numeric columns in your report in one pass of the data. For BY TOTAL to work correctly, you must have an aggregating display command such as SUM. A non-aggregating display command, such as PRINT, simply retrieves the data without aggregating it. Records are sorted in either ascending or descending sequence, based on your query. Ascending order is the default.
You can also use the BY TOTAL phrase to sort based on temporary values calculated by the COMPUTE command.
Note: On z/OS, the sort on the aggregated value is calculated using an external sort package, even if EXTSORT = OFF.
[RANKED] BY [HIGHEST|LOWEST [n] ] TOTAL {display_field|COMPUTE name/format=expression;}
where:
A BY TOTAL field is treated as a display field when the internal matrix is created. After the matrix is created, the output lines are aggregated and re-sorted based on all of the sort fields.
In this example, the salary average is calculated and used as a sort field. The two highest salaries are displayed in the report.
TABLE FILE EMPLOYEE SUM SALARY CNT.SALARY BY DEPARTMENT BY HIGHEST 2 TOTAL AVE.SALARY AS 'HIGHEST,AVERAGE,SALARIES' BY CURR_JOBCODE END
The output is:
In this example, the salary average is calculated and used as a sort field. The two highest salaries are displayed and ranked.
TABLE FILE EMPLOYEE SUM SALARY CNT.SALARY BY DEPARTMENT RANKED BY HIGHEST 2 TOTAL AVE.SALARY AS 'HIGHEST,AVERAGE,SALARIES' BY CURR_JOBCODE END
The output is:
In this example, the monthly salary is calculated using a COMPUTE within a sort field. The two highest monthly salaries are displayed.
TABLE FILE EMPLOYEE SUM SALARY CNT.SALARY BY DEPARTMENT BY HIGHEST 2 TOTAL COMPUTE MONTHLY_SALARY/D12.2M=SALARY/12; AS 'HIGHEST,MONTHLY,SALARIES' BY CURR_JOBCODE END
The output is:
WebFOCUS |