Sorting the Rows of a Report Down the Page

The simplest report requests either print or add up all the records in the data source. The report has one line for each record retrieved or one line displaying the total for each numeric field.

You can reorganize the way the records are printed by sorting them using the data found in another field. To do this, you type the keyword BY followed by the name of the field you want to sort, the sort field.

For example, issue a report request that adds up the hours spent in the classroom for each job category in the data source. Classroom hours are stored in the field ED_HRS. Job categories are stored in the field CURR_JOBCODE. Use AS to rename the column title the sort field CURR_JOBCODE creates.

Issue the following request:

TABLE FILE EMPLOYEE
SUM ED_HRS
BY CURR_JOBCODE AS 'JOB,CATEGORY'
END

Run the request. The output is:

If you execute this request without using BY, a single number displays, which represents the total number of classroom hours for the entire data source. However, the report now shows the total number of classroom hours logged in each job category. You could just as easily analyze the numbers by department or by employee.

Notice how sorting affects the output of the request. A total for each job category retrieved from the data source is produced. The sort order is low to high, by default.

Now use the verb LIST with a sort phrase. When you list records, they are printed next to their corresponding sort field value and the list counter is set to one (1) each time the value for the first sort field specified in the request changes.

For example, issue a report request that lists the date and percent of salary increases associated with each job category in the data source. The increase date is stored in the field DAT_INC and the percentage increase is stored in the field PCT_INC.

Issue the following request:

TABLE FILE EMPLOYEE
LIST DAT_INC PCT_INC
BY CURR_JOBCODE AS 'JOB,CATEGORY'
END

Run the request. The output is:

Note: By default, a sort field value displays only on the first row or column of the set of detail rows or columns generated for that sort field value. If you want the sort field values to display for every row, issue the SET BYDISPLAY = ON or ON TABLE SET BYDISPLAY ON command in your request.

Issue the following request:

TABLE FILE EMPLOYEE
LIST DAT_INC PCT_INC
BY CURR_JOBCODE AS 'JOB,CATEGORY'
ON TABLE SET BYDISPLAY ON
END

Run the request. The output is: