Sorting in APEX: A deep dive into Classic Reports and Card Regions

Contents

In this article, I’m going to dive into Classic Report sorting in Oracle APEX. I’ll tell you what it is, and how it works – including all the options for both users and developers.

This is part 1 of a series of blogposts entitled Things in APEX I’ve used for years, but not fully understood how they work – catchy right? 😎 The article will have a slightly different structure than my previous pieces: I’ll point out questions I asked myself – often ones that may come to your mind while reading – and then provide answers.

Preparation – creating a Classic Report

Let’s start by creating a Classic Report on a SQL Statement using the wizard.

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP

I’m going to give it a static ID of EMP. You’ll see why later.

It looks like this:

Note that the sorting has been defaulted to (the first column) ENAME. And look! It has an icon:

Deep dive and questions

Now, let’s take a deeper look at this report and consider the – both big and small – questions that come to mind.

Why does the sorting shown above happen?

Well, at the column level, these 4 columns have been given the Default Sequence of 1, Ascending.

Why these 4 columns specifically?

I’m not sure. If you want me to make a guess there are two reasons:

All Strings
All Foreign Keys

I’ve tested a few tables and this seems to be the case.

Note: The other 3 columns are sortable, however, they do not have a default sequence.

If 4 columns share the same Sort Sequence, how does APEX decide to specifically place the sort on ENAME ascending?

The default is the column with the lowest Default Sequence. If there is a tie, it’s the lowest Sequence.

Look at the picture above again and notice that there is a 4-way tie for the lowest Default Sequence, however, ENAME wins as it’s the first in the list, i.e., it has the lowest Sequence.

What SQL did it actually run?

This one:

select i.*
 from (select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
from(select /*+ qb_name(apex$inner) */d."EMPNO",d."ENAME",d."JOB",d."MGR",d."HIREDATE",d."SAL",d."COMM",d."DEPTNO" from(select x.* from "EMP" x 
)d
 )i 

)i where 1=1 
order by "ENAME" asc nulls last,"JOB" asc nulls last,(select "ENAME"from (select "ENAME","EMPNO"
from(select x.* from "EMP" x 
 )i 
)where "EMPNO"=i."MGR") asc nulls last,(select "DNAME"from (select "DNAME","DEPTNO"
from(select x.* from "DEPT" x 
 )i 
)where "DEPTNO"=i."DEPTNO") asc nulls last

Note: In the Order By clause, the columns appear in the same order as I previously described: ENAME, JOB, MGR, DEPTNO. It’s just that EMPNO gets to claim that up arrow icon.

So what happens when the user tries to sort using another column like Salary (not one of the four I’ve listed above)?

Let’s see.

select i.*
 from (select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
from(select /*+ qb_name(apex$inner) */d."EMPNO",d."ENAME",d."JOB",d."MGR",d."HIREDATE",d."SAL",d."COMM",d."DEPTNO" from(select x.* from "EMP" x 
)d
 )i 

)i where 1=1 
order by "SAL" asc nulls last,"ENAME" asc nulls last,"JOB" asc nulls last,(select "ENAME"from (select "ENAME","EMPNO"
from(select x.* from "EMP" x 
 )i 
)where "EMPNO"=i."MGR") asc nulls last,(select "DNAME"from (select "DNAME","DEPTNO"
from(select x.* from "DEPT" x 
 )i 
)where "DEPTNO"=i."DEPTNO") asc nulls last

Right. So, now, the order is like this: SAL, ENAME, JOB, MGR, DEPTNO. I.e., it’s the chosen column followed by the famous four.

What happens when the user tries try to sort using one of the four columns (e.g., MGR)?

Let’s check.

select i.*
 from (select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
from(select /*+ qb_name(apex$inner) */d."EMPNO",d."ENAME",d."JOB",d."MGR",d."HIREDATE",d."SAL",d."COMM",d."DEPTNO" from(select x.* from "EMP" x 
)d
 )i 

)i where 1=1 
order by (select "ENAME"from (select "ENAME","EMPNO"
from(select x.* from "EMP" x 
 )i 
)where "EMPNO"=i."MGR") asc nulls last,"ENAME" asc nulls last,"JOB" asc nulls last,(select "DNAME"from (select "DNAME","DEPTNO"
from(select x.* from "DEPT" x 
 )i 
)where "DEPTNO"=i."DEPTNO") asc nulls last

Now the order is like this: MGR, ENAME, JOB, DEPTNO. MGR jumps the queue and the remaining order is preserved – clever, right?

What are all those “nulls last” doing in the code?

These can be found in the Attributes tab.

Can I change the Ascending/Descending for each column?

Note: If you are following along at home, first click to sort Employee Name Ascending before continuing for maximum effect.

Yes. You can change ENAME to sort Descending.

Give it a run and… it looks exactly the same. 😕 No change at all. It’s not sorting Descending like I asked for.

Why doesn’t it work?

Because the sorting is controlled by a permanent user preference that already has this set to Ascending. To see this preference, create a Dynamic Region Content with this SQL:

DECLARE
    l_region_static_id_c    CONSTANT VARCHAR2(512) DEFAULT 'EMP';
    l_preference_template_c CONSTANT VARCHAR2(512) DEFAULT 'FSP&APP_ID._P&APP_PAGE_ID._R%0_SORT';
    l_preference_name       VARCHAR2(512) DEFAULT NULL;
    l_preference_value      VARCHAR2(512) DEFAULT NULL;
BEGIN

    FOR x IN ( 
        SELECT region_id  
          FROM apex_application_page_regions
         WHERE application_id = :APP_ID 
           AND page_id = :APP_PAGE_ID
           AND static_id = l_region_static_id_c
    )
    LOOP
        l_preference_name := apex_plugin_util.replace_substitutions( apex_string.format(l_preference_template_c, x.region_id ) );
        l_preference_value := apex_util.get_preference(      
            p_preference => l_preference_name,
            p_user       => :APP_USER);

            RETURN '<p><span class="u-bold"> Preference Name: </span>' ||l_preference_name || '</p>' ||
                    '<p><span class="u-bold"> Preference Value: </span>' ||l_preference_value || '</p>';
    END LOOP;
END;

There we go:

Note that column 1 is the Hidden Column EMPNO, therefore column 2 (i.e., ENAME) is stored instead.

If the Column Sequence Number is stored (i.e., column 2) and not the Column Name (e.g., ENAME), does that mean you can change the Column Sequence and the user’s sorting will now default to another column?

Yes, you can freely order the Column Sequence (i.e., Column Name > Layout > Sequence) without affecting the user’s default sort at all. 👌

The number in the User Preference Value, i.e., sort_2_asc, references the Column Order defined in the Region SQL Statement. Therefore, if I were to change the SQL statement to this:

select EMPNO,
       JOB,   -- JOB is now column 2. This used to be column 3
       ENAME, -- ENAME is now column 3. This used to be column 2
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP

… then my users would start complaining to me because their preference is still to sort on column 2, however, column 2 is now JOB and not ENAME.

To the users, I’ve broken their sorting.

My table has 8 columns. What happens if I add a new 9th column, the users start sorting on that 9th column, and then I drop that column?

Let’s try.

alter table "EMP" add
("HIGH_JUMP_PERSONAL_BEST" NUMBER);

Note. My High Jump best is 1.68m which I obtained when I was 15 years old. I have come to terms that I will never beat it. 😢

Now, I adjust my Region Query (to add the column) and add a Sort for my user.

You can see the preference value is now sort_9_asc.

I drop the column:

alter table "EMP" drop column "HIGH_JUMP_PERSONAL_BEST";

When I adjust my Region Query (to remove the column), the result looks like this:

Well, no Blue Screen of Death, however the sort has now been placed on the final column. The same thing happens when I set it sky-high:

BEGIN
    APEX_UTIL.SET_PREFERENCE(        
        p_preference => 'FSP192_P14_R40495703897447889_SORT',
        p_value      => 'sort_1919_desc',      
        p_user       => :APP_USER); 
END;

Interestingly, even though column 1919 doesn’t exist, APEX still honors the sort direction (i.e., Asc or Desc) on the final column.

If you use an invalid sort direction like this one:

BEGIN
    APEX_UTIL.SET_PREFERENCE(        
        p_preference => 'FSP192_P14_R40495703897447889_SORT',
        p_value      => 'sort_5_lufc',      
        p_user       => :APP_USER); 
END;

… then it defaults the sort order to Ascending.

If you specify an invalid column number:

BEGIN
    APEX_UTIL.SET_PREFERENCE(        
        p_preference => 'FSP192_P14_R40495703897447889_SORT',
        p_value      => 'sort_leeds_asc',      
        p_user       => :APP_USER); 
END;

… the user gets the standard sorting, however, no icon is placed next to any column. Funnily enough, the absolute values of column numbers are used.

BEGIN
    APEX_UTIL.SET_PREFERENCE(        
        p_preference => 'FSP192_P14_R40495703897447889_SORT',
        p_value      => 'sort_-5_asc',      
        p_user       => :APP_USER); 
END;

In the above example, this has the same effect as column 5 (Hired – remember there is one hidden column) being set as the default sort.

Can I delete all these User Preferences which are used for Sorting?

Yep. Stick this in a Before Header Process:

BEGIN
    APEX_UTIL.REMOVE_SORT_PREFERENCES(:APP_USER);
END;

Warning! ⚠️ This command deletes all sorts of preferences for the current user for all regions, not only on the current page but for all application pages across all workspace applications. Wild, right? Well, not that wild because it removes the preference from a User – not the application.

Can I clear user preferences a bit more selectively?

Yep. Use something like this to pick them and remove them (this code identifies a region to clear by its Static ID):

DECLARE
    l_region_static_id_c    CONSTANT VARCHAR2(512) DEFAULT 'EMP';
    l_preference_template_c CONSTANT VARCHAR2(512) DEFAULT 'FSP&APP_ID._P&APP_PAGE_ID._R%0_SORT';
    l_preference_name       VARCHAR2(512) DEFAULT NULL;
    l_preference_value      VARCHAR2(512) DEFAULT NULL;
BEGIN

    FOR x IN ( 
        SELECT region_id  
          FROM apex_application_page_regions
         WHERE application_id = :APP_ID 
           AND page_id = :APP_PAGE_ID
           AND static_id = l_region_static_id_c
    )
    LOOP
        l_preference_name := apex_plugin_util.replace_substitutions( apex_string.format(l_preference_template_c, x.region_id ) );
        apex_util.remove_preference(      
            p_preference => l_preference_name,
            p_user       => :APP_USER);
    END LOOP;
END;

Can I stop APEX saving the user preferences for sorting altogether?

No. But you could try this as an Application Process running On Load: Before Header to automatically remove the user preference for all regions before the page loads. This technique simulates the User Preferences never being used.

DECLARE
    l_preference_template_c CONSTANT VARCHAR2(512) DEFAULT 'FSP&APP_ID._P&APP_PAGE_ID._R%0_SORT';
    l_preference_name       VARCHAR2(512) DEFAULT NULL;
    l_preference_value      VARCHAR2(512) DEFAULT NULL;
BEGIN

    FOR x IN ( 
        SELECT region_id  
          FROM apex_application_page_regions
         WHERE application_id = :APP_ID 
           AND page_id IN ( :APP_PAGE_ID, 0 )
    )
    LOOP
        l_preference_name := apex_plugin_util.replace_substitutions( apex_string.format(l_preference_template_c, x.region_id ) );
        apex_util.remove_preference(      
            p_preference => l_preference_name,
            p_user       => :APP_USER);
    END LOOP;
END;

Can I clear the sorting using Reset Pagination or Clear Cache?

Do you mean this?

No, as you can see, it doesn’t work.

Matt, in your code, how did you know what the Preference Template was called?

I just looked in Administration > Manage Service > Session State > Preferences by User and I found examples like this one.

Sort Preferences are saved for my User – including my own. How can I see what the Default Sorts are for a new user?

First, here is what doesn’t work:

Logging out of APEX & back in again
Restarting your Browser
Deleting browser cache & cookies
Using another browser
Using Incognito mode / private browsing

Here are 3 options that do work:

1. Create a new user and log in using their account

This will work only once. Once the new user views the report, a new User Preference will be created

2. Duplicate the Report Region in Page Designer

This generates a new Component ID and, therefore, a new User Sort Preference. It may be useful to Duplicate & Delete the region several times before finally settling on your preferred defaults

3. Use one of the code samples I provided in this blog with the Build Option (as desired)

Use APEX_UTIL.REMOVE_SORT_PREFERENCES to remove all user preferences. i.e the wild approach
Clear selectively based on Static ID
Use an Application Process to clear all Sort preferences for the current page (and Page Zero)

What happens if I use my own Order By in the SQL?

Let’s try:

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP
ORDER BY ENAME

When I give it a run, I see this:

Amazing. It’s now sorting by Employee Name… However, not so amazing, as now the user cannot sort the columns. 😠

Let’s try adding them back:

Oh dear, my favorite thing – errors.

There is a very good explanation for this that is definitely worth reading. However, for now, I’ll put the attributes back manually and remove the Order By clause.

What’s all this stuff in Source > Order By Item?

You mean this:

Which opens this:

What’s this P6_ORDER_BY? I don’t have a P6_ORDER_BY Page Item! Oh man, the Advisor is going to go nuts when it sees this…

Or… not? Wait, what? It seems it doesn’t even care! 🥀

Okay, seriously now – the fact is, so far, we’ve been working with a Classic Report Region Type with a Theme Standard Template. This provides users with the ability to sort columns, as described above, using column headers (see below).

However, what Source > Order By Item does, is limit the Order By Controls to a Single Page Item, usually a Select List. You may want to do this in some circumstances.

Let’s do it now:

Create a Select List Page Item (basically anything that supports a List of Values) in the EMP region with the same name as the Item name in Source > Order By Item. In my case its P6_ORDER_BY
Set the List of Values Type to Static Values and accept the default values. Basically, don’t change them

Set the attributes Display Extra Values and Display Null Value to the Off position

In the Region (EMP) > Source > Order by Item set it to these values and click OK

By the way, the Key has to be unique in this popup.

Take a peek at the P6_ORDER_BY > List of Values > Static Values. See they have synchronized with Region (EMP) > Source > Order by Item

Fabulous. Now every time you use the Select List, it applies the Clause, e.g., ENAME ASC.

Run the page

Now, the Select List can only be used to apply predefined sorts. Be aware that column header sorting is now disabled.

Is this better than what I had before?

Personally, no, I don’t think it is. I think column heading sorting provides much more functionality to the user.

Why is it even a feature of APEX then?

The answer is that not all Classic Reports use the Theme > Standard template. Some use the Theme > Cards template, others the Theme > Comments template.

Some use the Cards Region Type (this is a better choice as APEX doesn’t insist on specifically naming column aliases).

All these types don’t have column headings and therefore are not sortable by the user. However, users may still require some type of sorting and developers would also like a declarative option. And the Source > Order by Item feature is the answer to all these dilemmas.

Look how useful it is on a Cards Region. The Order By Select List was created automatically by the wizard – it’s not the most beautiful of card lists… but you get the idea.

Does Source > Order By Item also create a User Preference which APEX then uses to default the Select List next time they use this page?

APEX does not create a User Preference for Source > Order By Item. However, it does submit the Value to Session, meaning that this setting persists for the duration of the user session. If the user logs out and logs back in again, then it’s back to the item’s initial default value – not any user preference.

Conclusion

I hope that’s given you a huge deep dive into Classic Report column sorting. I’ve learned a lot by writing this. And it was strangely fun to interview myself! One last tip: if you are experiencing Classic Report or Card Sorting that isn’t responding to your settings try the Selective Preference Delete Code in a Before Header Page Process.

And if you’re interested in Oracle APEX content, check out some of the other publications on the Pretius blog: