This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Apr 8, 2009 7:43 AM by 388375 RSS

Need to do column SORTING only when I click the column heading

DeepakJ Newbie
Currently Being Moderated
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
  • 1. Re: Need to do column SORTING only when I click the column heading
    515357 Explorer
    Currently Being Moderated
    Deepak,

    The order in which the database will return the rows is not guaranteed, I think. It will return in any order unless you specify ORDER BY clause.

    For your example, did you specify the Sort Sequence as well?

    Ravi
  • 2. Re: Need to do column SORTING only when I click the column heading
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    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
    5. create a process on load like in my example here:
    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
    -------------------------------------------------------------------
  • 3. Re: Need to do column SORTING only when I click the column heading
    DeepakJ Newbie
    Currently Being Moderated
    Hi Ravi,

    I am using

    select * from table
    start with empno = "XXXX"
    connect by prior empno = mgr
  • 4. Re: Need to do column SORTING only when I click the column heading
    Tyler Expert
    Currently Being Moderated
    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.
  • 5. Re: Need to do column SORTING only when I click the column heading
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    I think this example gives you exactly that what you were asking for:

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

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 6. Re: Need to do column SORTING only when I click the column heading
    DeepakJ Newbie
    Currently Being Moderated
    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
  • 7. Re: Need to do column SORTING only when I click the column heading
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Deepak,

    You are forcing me to increase the number of my postings. This time there is no real need
    to answer since everything is explained in the example:
    4. Page Process On-Load clears the default sort preference once per session.
    It is conditional upon :P13_PREFERENCE_REMOVED IS NULL
    
    DECLARE
       v_sort_pref   VARCHAR2 (200)
          :=    'FSP'
             || :app_id
             || '_P'
             || :app_page_id
             || '_R'
             || :p13_region_id
             || '_SORT';
    BEGIN
       HTMLDB_UTIL.remove_preference (v_sort_pref, :app_user);
       :p13_preference_removed := v_sort_pref;
    END;
    So the
       HTMLDB_UTIL.remove_preference (v_sort_pref, :app_user);
    will do that.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 8. Re: Need to do column SORTING only when I click the column heading
    DeepakJ Newbie
    Currently Being Moderated
    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
  • 9. Re: Need to do column SORTING only when I click the column heading
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    A button could redirect to the page and issue a request. An onload process could fire upon request. A button could also be a submit button and it would then need an on submit process. It is your choice which way you go.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 10. Re: Need to do column SORTING only when I click the column heading
    DeepakJ Newbie
    Currently Being Moderated
    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.
  • 11. Re: Need to do column SORTING only when I click the column heading
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    An onload process will always fire if it is not conditional. If it is conditional upon request, it will fire if the right request has been issued.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 12. Re: Need to do column SORTING only when I click the column heading
    DeepakJ Newbie
    Currently Being Moderated
    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
  • 13. Re: Need to do column SORTING only when I click the column heading
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Deepak,

    In my example it does work.
    In your example (which I can't view) it doesn't.

    This would mean you missed something. You have a login to my workspace and the full
    explanation of the code. I would recomend copying my example 1 to 1 and doing a sort of
    excercise first. You would need to have the same objects and the same processes as I do.
    There is nothing on my page I wouldn't need. So, this is the first place to look into. There
    are many things that could go wrong:

    1. do you get the right region_id
    2. is your sorting preference name the right one
    3. is the condition for your process set correctly
    4. and many others

    Unless you give me access to your application and I have a look.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 14. Re: Need to do column SORTING only when I click the column heading
    DeepakJ Newbie
    Currently Being Moderated
    Ok..let me start in a fresh. Might be I have missed something.
1 2 Previous Next