3 Replies Latest reply: Aug 28, 2014 8:28 AM by Mike Kutz RSS

    Rolling Months in Interactive Report


      Hi everyone. I'm relatively new to Application Express, having only developed two applications so far. My current application is a bit more complex than the previous ones and has some challenging requirements I need to overcome. One of the screens is required to show a list of projects with particular fields, and then alongside each a rolling 18 month view of capacity figures. The way I and my work colleague have decided to tackle this is to store the project details in one table and then link across to another table that stores the capacity figures. Each row in this capacity table contains the Project ID which links back to a record in the main table, a field identifying which month the record is for, and then the value. My colleague has then built a complex SQL statement which brings back a rolling 18 months of figures from this table based on the current system date. However he says that he cannot find a way to bring back dynamic field names for the month values. Currently the SQL returns the required project fields and then alongside them fields named Month1 to Month18.


      I wish the main screen to be an interactive report screen which will lead to an amend record screen via the usual method. I can of course link this SQL to an interactive report, but the months will be displayed with titles Month1 to Month18. What I want the screen to display is the relevant months and years in MMM YY format e.g. if ran today it would show Aug 14, Sep 14, Oct 14 etc. If ran next month it would show Sep 14, Oct 14, Nov 14 etc.


      The other issue is of course the amend record screen. I need the dynamic field names to appear on this too, and for the link between the two screens to work in the usual manner.


      Does anyone know how to do this without the need for complex Java code? Is there an automated method for doing rolling months in APEX? If not I am surprised, as I feel it is something which would often be required for reporting. If you know of a method by which rolling months can be used with an interactive report which is totally different to the way we currently have the tables set up, I am more than happy to change whatever is necessary to get it to work. Please remember that I am still new to APEX so I will need a detailed step-by-step solution to this issue that I can follow.


      Thanks in advance

        • 1. Re: Rolling Months in Interactive Report
          Mike Kutz


          I suspect your colleague is using PIVOT to generate the required data.


          As I see it, you have three options to build dynamic table

          Basic Report

          Basic Report allows you to use one PL/SQL function to generate the column names and another PL/SQL function to generate the SQL.

          I suggest your colleague puts them both in a Package for easier maintenance.


          Pivot XML

          Instead of using PIVOT, you use PIVOT XML.

          The output data is XML.  This gets transformed to HTML via XMLTransform

          fac586 is responsible for this solution which he posted here:  Matrix report



          I haven't seen anybody post one up at http://www.apex-plugin.com/

          BTW - you'll want to use jQuery, not Java.



          • 2. Re: Rolling Months in Interactive Report

            Hi Mike, thanks for your tips on possible solutions. Unfornately I don't know how to start with any of that as I'm relatively new to APEX. Do you know where I can view a working application with rolling months and delve into the screens to see how its been set up?

            • 3. Re: Re: Rolling Months in Interactive Report
              Mike Kutz

              I highly recommend you create an account on http://apex.oracle.com/

              This way you can create a very small app and let others take a look and help you out.

              (make sure you create a developer's account so you don't have to post your email address for the spam-bots)


              Sample page using Option 1


              username/password:  demo/demo


              Note:  This app is for testing/demoing various Date related stuff.

              I'm currently setting the database session to use Norwegian as the language for TO_DATE/TO_CHAR functions.

              (eg October comes out as Oktober)


              General implementation:

              use a Classic Report with "Generic Column Names"

              if the SQL or the column name generating function requires the value of an Item, make sure you place it in the "Items to Submit" section

              In the Report Attributes tab for the report, set the "Headings Type" parameter to be "PL/SQL"

              Don't forget to fill out the appropriate PL/SQL code.


              Demo App uses:

                l_buffer varchar2(4000);
                with date_generator as (
                  -- generate 8 dates (1 per column)
                  select trunc( add_months( to_date( :P3_REFERENCE_DATE, 'DD-MM-YYYY'), - (level - 1) ), 'MONTH' )
                    as date_for_column
                  from dual connect by level <= 8
                -- LISTAGG helps converting the result to the required "colon separated list"
                select listagg( to_char( date_for_column, 'fmMonth YYYY'), ':')
                  within group (order by date_for_column)
                  into l_buffer
                from date_generator;
                return l_buffer;


              To view the details of the app that is hosted on http://apex.oracle.com/


              workspace:  MIKEKUTZ_TEST

              developer name:  developer

              developer password:  trymeout