Sorting Order in Oracle APEX Classic Report

In APEX, the sorting option for a classic report is divided into two aspects: 

1. How records will be sorted when the report first renders?
2. Whether the end-user can change the sort order interactively by clicking the column header.

A classic report can be developed using either the static query or the dynamic query and the sorting behavior is different for both options. First, we will see the sorting behavior for the report based on the static query. 

The below classic report is based on the static query and displays all the records of the EMP_DUP table. For this blog, I created EMP_DUP which is the same as the EMP table but contains duplicate records.

The sorting aspects for this report are determined by the properties in each column's sorting section. The below image shows the sorting section for JOB.

The Default Sequence property determines the initial sort order. Each column that participates in the initial sort order will have a unique sequence number that will determine the sort order when the report will render. By default, the value of this property is null for all the columns so when the report will render, it will be sorted by the sequence of the column in the rendering tree.

In this report, the first column in the rendering tree is the EMPNO and the report is sorted by it. 

Now, I am setting the Default Sequence property for the JOB column to 1 and the ENAME column to 2. The report will be rendered with the rows sorted by JOB, and two employees having the same JOB will be sorted by ENAME.

The Sortable property below the Default Sequence property specifies the second aspect: whether an end-user can interactively sort the report by clicking the column header. By default, all columns are sortable. Click on the column header to sort the report by a different column. 

It might be possible that the changes that you made for the Default Sequence property will not be reflected when you run the report next time or after changing the sorting option by clicking on a column header, the report will be sorted by that column always on a subsequent run. This is because APEX will store the sorting preference and it will use it next time when you run the report.  To tackle this behavior, use APEX_UTIL.REMOVE_SORT_PREFERENCES procedure. Create a Before Header process and call this procedure which will remove the sort preferences.

One thing to note here that the source query that I used in this report doesn't have an ORDER BY clause. When the report's source query has an ORDER BY clause, the records are sorted by that order only. The Sortable property will be disabled for all the columns and the end-user cannot sort the report by clicking the column header.

When you try to enable Sortable property, you will get the below error.

Now let's understand the sorting behavior for the report with dynamic query. Below is the report that I created using the dynamic query by selecting the source type Function Body returning SQL Query. Please note that the Use Generic Column Names property is disabled.

If the query returned by the function has an ORDER BY clause and the Use Generic Column Names property is disabled then the sorting behavior is the same as the sorting behavior of the static query having ORDER BY clause which implies the ORDER BY clause takes precedence and the sortable property of each column will be turned off. As you can see that the below report is sorted by EMPNO.

On the other hand, if the Use Generic Column Names property is enabled and the columns are generic, then the column's Sortable property takes precedence. The Sortable property is turned on for all the columns and the ORDER BY clause in the source query is ignored. ORDER BY clause will only take into account when the Sortable property of each column has been turned off.

Once you enabled Use Generic Column Names Property, the Column name in the rendering tree will be changed to COL01, COL02, COL03, and so on. 

 I set the Default Sequence property of COL03 which is JOB to 1, and COL02 which is ENAME to 2. I also set the Heading property of this report as shown in the below image.

Now the report will be sorted by JOB and ENAME as shown in the below image.

For more detail on the report with a dynamic query, refer to my previous blog.