4 Replies Latest reply on May 17, 2012 9:26 PM by 937995

    Control break behavior


      Can anyone tell if there's a way to override the default behavior of Control Break?
      In my report (a schedule), I have a column that holds the values for days (monday, tuesday, etc.).
      I want to control break on that column but keep the resulting grups ordered in a natural order (monday first, tuesday second and so on).
      I can write the sql that generates my report that way by using decode, but once I Control Break apex is applying a new order by clause on my report, destroying my order.
      Any ideas on how to tell apex how to order for control break using decode, or how to disable ordering entirely when I use Control Break on a specific column?

      Thank you!

      Edited by: user2130586 on May 17, 2012 6:40 AM

      Edited by: user2130586 on May 17, 2012 6:41 AM
        • 1. Re: Control break behavior
          Welcome to the forum: please read the FAQ and forum sticky threads (if you haven't done so already).

          When you have a problem you'll get a faster, more effective response by including 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>Region/item type(s)

          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.

          When asking a question about "reports" it's firstly essential to differentiate between standard and interactive reports. Which is it?

          I suggest you take advantage of the opportunities provided by apex.oracle.com and reproduce the problem there...
          • 2. Re: Control break behavior
            Thankx for the heads-up.
            My test app is running on apex.oracle.com and the report is an interactive one.
            Here's a link to it: http://apex.oracle.com/pls/apex/f?p=27121:17:1262402719368101:::::
            What I'm trying to do is apply a control break on the ZI column and have the resulting groups show up in a specific (natural) order. The ZI column translates to DAY and the order is supposed to be as follows:
            As far as I see, when applying a control break to a column, apex is doing an order by on that column to make sure the groups are formed correctly.
            My question is: Is there a way to tell apex to use decode in the order by it applies on the column, or to have it skip the order by when doing a control break?

            Thankx again!

            Edited by: DragosPaul on May 17, 2012 1:39 PM
            • 3. Re: Control break behavior
              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 along the lines you suggest.

              Modify the query to generate the ZI column equivalent to:
                 '<!-- '
              || case zi
                   when 'luny' then '1'
                   when 'marti' then '2'
                   when 'miercuri' then '3' 
                   when 'joi' then '4'
                   when 'vineri' then '5' 
                   when 'sambata' then '6'
                   when 'duminica' then '7'
              || ' -->'
              || zi
              i.e. prefix the day 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 ZI column's Display Text As property to Standard Report Column so APEX won't escape the HTML tags.

              The Control Break (and sorting) should now follow the natural ordering.

              Also consider using standard reports which are more controllable (especially with a custom report template).
              • 4. Re: Control break behavior

                That's a really good suggestion! Never thought about tricking it like that, by using an html comment.

                Thankx again!