1 2 Previous Next 20 Replies Latest reply: Apr 8, 2009 9:43 AM by 388375 RSS

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

    DeepakJ
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    Ok..let me start in a fresh. Might be I have missed something.
                                    1 2 Previous Next