6 Replies Latest reply: Sep 16, 2013 8:00 AM by vdsadhi RSS

    dynamic report

    prog

      Hi,

      I have a report  which is appearing according to the period I define ( from date - to date)

      Now I would like to add a display only field which will calculate the sum of one of the columns in the report.

      so each time it will change depending on the report.

      can any one help

        • 1. Re: dynamic report
          prog

          Here is the report. I want to add a display field below the table which add all the payments every time I run the report.

          Is it possible??

           

          Episodeid

          Payment

          Dateofpayment

          287200003-SEP-13
          5027600008-SEP-13
          49477023.208-SEP-13
          4951008-SEP-13
          50211200008-SEP-13
          50231000008-SEP-13
          50251236008-SEP-13
          4991310008-SEP-13
          49951000009-SEP-13
          50431500009-SEP-13
          4996009-SEP-13
          49951759.5709-SEP-13
          4996183609-SEP-13
          50481300009-SEP-13
          5041700009-SEP-13
          • 2. Re: dynamic report
            vdsadhi

            It's possible. You can create a process in 'On Load- Before Header',

             

            BEGIN

            select sum(payment) into :P2X_TOT_PAY from TABLE_NAME where DATEOFPAYMENT between :P2X_FROM_DATE and :P2X_TO_DATE;

            EXCEPTION

            when no_data_found then

            :P2X_TOT_PAY:=0;

            END;

            Please check my example here,

             

            http://apex.oracle.com/pls/apex/f?p=35140:23:7170909123631:::::

             

            Username: test_sa

            Pwd: test123

             

            Use the date range from 01-Jun-13 to 20-Jun-13..

             

            Thanks

            Lakshmi

            • 3. Re: dynamic report
              prog

              I did and I got this error

              ORA-01830: date format picture ends before converting entire input string

              • 4. Re: dynamic report
                vdsadhi

                r u using any date format in date picker???

                • 5. Re: dynamic report
                  prog

                  Yes

                  the format is DD-MON-YYYY HH:MIPM

                  • 6. Re: dynamic report
                    vdsadhi

                    okay..

                     

                    Try this,

                    BEGIN

                    select sum(payment) into :P2X_TOT_PAY from TABLE_NAME where trunc(DATEOFPAYMENT) between TO_DATE(:P2X_FROM_DATE,'DD-MON-YYYY HH:MIPM') and TO_DATE(:P2X_TO_DATE,'DD-MON-YYYY HH:MIPM');

                    EXCEPTION

                    when no_data_found then

                    :P2X_TOT_PAY:=0;

                    END;

                     

                    use to_date function in your report query as well.. It will work...

                     

                     

                    Thanks

                    Lakshmi