4 Replies Latest reply: Oct 3, 2012 4:42 AM by foobarbaz RSS

    Interactive report - order column by other column values

    konrad_wroc
      Hi Oracle experts,

      could you please advise how to order column A by values of column B in Interactive report ?

      When clicking on header of "Countries" column in order to sort it acs/desc I would like to order it by values of column "Sort_order" which stores values from 1 to 100.

      The goal is to order "Countries" not alphabetically, but according to values of "Sort_order" column.


      Any ideas?


      Thanks in advance for your help.

      Best regards,
      Konrad
        • 1. Re: Interactive report - order column by other column values
          fac586
          962622 wrote:
          Hi Oracle experts,
          Welcome to the forum: please read the FAQ and forum sticky threads (if you haven't done so already), and update your profile with a real handle instead of "962622".

          When you have a problem you'll get a faster, more effective response by providing as much relevant information as possible upfront. This should include:

          <li>Full APEX version
          <li>Full DB/version/edition/host OS
          <li>Web server architecture (EPG, OHS or APEX listener/host OS)
          <li>Browser(s) and version(s) used
          <li>Theme
          <li>Template(s)
          <li>Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)

          With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
          could you please advise how to order column A by values of column B in Interactive report ?

          When clicking on header of "Countries" column in order to sort it acs/desc I would like to order it by values of column "Sort_order" which stores values from 1 to 100.

          The goal is to order "Countries" not alphabetically, but according to values of "Sort_order" column.

          Any ideas?
          I'm not an expert on Interactive Reports, so there may be a less brute force method using the built-in features, but here's an approach that's worked in the past.

          Modify the query to generate the column as:
          '<!-- '|| to_char(sort_order, '009') || ' -->' || country
          i.e. prefix the country name with an HTML comment containing the required ordinal number. This won't be visible in the report, but will be considered when sorting in SQL.

          In the IR Column Attributes set the column's Display Text As property to Standard Report Column so APEX won't escape the HTML tags.

          This method has side effects: some IR filters won't work; aggregate calculations can't be applied to the column; and report exports contain the HTML rather than the expected value.

          Edited by: fac586 on 02-Oct-2012 13:28

          From APEX 4.2 IR columns have support for HTML Expressions, so Re: Report formatting/sorting issue using a hidden column and HTML Expression should be used, as it's purely declarative and provides better separation of concerns. (Still a problem on report exports though. Whilst the sort column can be suppressed using a condition, the hidden column can't be "unhidden" to replace it.)
          • 2. Re: Interactive report - order column by other column values
            konrad_wroc
            fac586,

            thank you for your response, however I'm looking for a different method as I need to include IR filter on that column as well.

            I've used similar approach as yours:
            <div class="hideMe508"> || to_char(sort_order, '009') ||</div> || country
            but when working with IR column filter and choosing one of values a whole html tag is displayed as a value of Country column instead of a given value.

            e.g.
             Country = <div class="hideMe508"> 010 </div> Italy
            instead of
            Country = Italy
            I'm using Apex 3.2 on Oracle 11g DB.


            Any ideas ?



            Best regards,
            Konrad
            • 3. Re: Interactive report - order column by other column values
              fac586
              962622 wrote:

              thank you for your response, however I'm looking for a different method as I need to include IR filter on that column as well.

              I've used similar approach as yours:
              <div class="hideMe508"> || to_char(sort_order, '009') ||</div> || country
              but when working with IR column filter and choosing one of values a whole html tag is displayed as a value of Country column instead of a given value.

              e.g.
               Country = <div class="hideMe508"> 010 </div> Italy
              instead of
              Country = Italy
              I'm using Apex 3.2 on Oracle 11g DB.


              Any ideas ?
              None, other than it appears to be time to start questioning whether it's sensible to use the unnatural sorting. What's more important: custom sort order or the convenience of IR features?
              • 4. Re: Interactive report - order column by other column values
                foobarbaz
                Hi,

                You are right, but sometimes it's hard to convince client that sorting by a value that user can't see is not the best idea.

                <font color="red">I don't recommend this solution to anyone.</font>
                Use only in case when something terrible can happen to you if you don't implement this ;)

                You have the following table:
                CREATE TABLE grades
                   (     gorder NUMBER(1, 0), 
                     gdesc VARCHAR2(10)
                   )
                /
                
                INSERT INTO grades VALUES (1, '&gt; A')
                /
                INSERT INTO grades VALUES (2, 'A')
                /
                INSERT INTO grades VALUES (3, '&lt; A')
                /
                After sorting by gdesc you would like to see
                '&lt; A'
                'A'
                '&gt; A'
                You can modify IR query and put some number of space characters in front of actual value.
                SELECT gid
                      ,LPAD(gdesc
                           ,LENGTH(gdesc) + gorder
                           ,' ') gdesc
                  FROM grades
                Be aware of the fact that the order is reversed: 3, 2, 1.

                Regards,
                Sebastian

                Edited by: foobarbaz on 2012-10-03 02:42