I have the Report based on the following query

- select dept, ename, empno from emp;

when I run this report it display's the result as follows without any order by/sorting

DEPT - ENAME - EMPNO (these are columns headings)

30 - abc - 1001
20 - bcd - 2001
10 - xyz - 3001

Now I had enabled the Sortable Column attribute (YES) for column DEPT.
So when the report is run for the first time...it should display the report in the same way as above (without any sorting) and when I explicitly click the column heading DEPT, then only it should do the sorting.

but this is not happening, as soon as I set the sortable attribute to YES for DEPT
and running the report for the first time..it's doing the sorting based on DEPT..as mentioned below.

10 - abc - 1001
20 - bcd - 2001
30 - xyz - 3001

My requirement is Report should always display the original sequence (in the same way if I run the report in TOAD or SQL*PLUS, ...) and when I click the column heading then only it should do the actual sorting.

Appreciate any inputs..

thanks,
deepak

You can do the following:

1. user rownum in your report and put it to be the first sortable column in you report - do that in the report attributes,

2. enable column sorting on it

3. hide this column

4. Get the region_id of your report using the following SQL Query for computing the hidden region id item:

SELECT region_id
  FROM apex_application_page_regions
 WHERE static_id = '99_1' AND page_id = :APP_PAGE_ID
[code]
 
5. create a process on load like in my example here:
 
[code]
DECLARE
   v_sort_pref   VARCHAR2 (200)
      :=    'FSP'
         || :app_id
         || '_P'
         || :app_page_id
         || '_R'
         || :p99_region_id
         || '_SORT';
BEGIN
   HTMLDB_UTIL.remove_preference (v_sort_pref, :app_user);
   :p99_preference_removed := v_sort_pref;
END;

This process will run only once per session on the page load - conditional if p99_preference_removed is NULL.

FSP31517_P99_R4748654222627511152_SORT is the item storing the current sorting
preference of you report. You need to dynamically set this item name which contains
the app_id, page_id and report_id,

See this example:

http://apex.oracle.com/pls/otn/f?p=31517:99

It will sort on invisible rownum and the sorting for the other columns are given. Sort on
any of them and log out. Login again and go to the same page. Default sorting will be
enabled again.

Denes Kubicek

---

http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://apex.oracle.com/pls/otn/f?p=31517:1

---

Depends on how you want to sort. The syntax of "order by" when using "connect by" is actually "order siblings by". APEX doesn't support this. So, you could still apply the standard order to the query, but understand that you'll lose the order of the hierarchy.

Hi Denes,

How you have dome the Remove Sort Preference in the above example

http://apex.oracle.com/pls/otn/f?p=31517:13

thanks,
deepak

Denes,

Actually I got the logic, my question is --- where should I write this PL\SQL code in case of Button (Remove Sort Preference) is pressed.

I mean when the button is pressed, how should I redirect it to On Load process.

thanks,
deepak

I create a button and redirect it the page and also created an onload process, but when I press the button..it's not removing the sorting preference.

for example - i have

empno, ename, dept. - i did the sorting on ename but when I logout & login again..the report is sorting itself by ename, it's not removing the sorting from ename. same thing with button also.

Denes,

I have removed the button for the time begin.

Now I just have the ON LOAD (Before Header) Procees with the above PL\SQL code and P4_REGION_ID , P4_PREFERENCE_REMOVED.

Everything I did exactly as mentioned.

Now when I login for the first time...it worked perfectly. Then I explicitly did the sorting on one of column (say empname). Now I logged out and login again I still have the sorting on empname column by default...same thing is happening with other columns. So the last column for which I did the sorting before logging out, it remains there when I logged back again.

Just want to know if I am missing something.

thanks,
deepak

Ok..let me start in a fresh. Might be I have missed something.