5 Replies Latest reply on Nov 3, 2018 10:26 AM by fac586

    Oracle Apex - Download CSV produces an empty report

    KatReed

      I am currently using Oracle Apex version 18.2.0.00.12 on Internet Explorer on a computer running Windows 10.

      I am having a problem with downloading an Interactive Grid as a CSV file.

      On my Apex page I have an interactive grid and three page items. Two of the page items, P200_DATE_FROM and P200_DATE_TO, are Date Picker items. The other item is a button named "Submit" and its action is "Submit Page".

      This is the query for my interactive grid:

       

      select * from kat_test
         where repairdate between :P200_DATE_FROM and :P200_DATE_TO;
      

       

      There are four columns in the Interactive Grid:

      Issue - Varchar2

      Month - Number

      Week - Number

      Repairdate - Date

       

      The only settings I changed in the Interactive Grid are the title and the SQL query.

       

      I changed the Source of P200_DATE_FROM to PL/SQL Expression that returns the first day of the current month. It is set to be used "Only when the current value in session state is null". The expression is:

       

      trunc(last_day(sysdate)-1, 'mm')
      

       

      I changed the Source of P200_DATE_TO PL/SQL Expression that returns the current date. It is also set to be used "Only when the current value in session state is null". The expression is:

       

      sysdate
      

       

      When I run the page, it loads exactly as expected in the sense that the P200_DATE_FROM page item is populated with the first date of the current month, the P200_DATE_TO item is populated with the current date, and the interactive grid displays the correct data.

      The problem: When I select Actions -> Download -> CSV, the CSV file downloads and opens as an Excel file, BUT only the header names are displayed. The cells underneath the headers are all empty. However, if I change one of the dates (or both) in the P200_DATE_FROM page item and/or the P200_DATE_TO page item and then press the submit button, the page is submitted and then when I click Actions -> Download -> CSV, the Excel file opens and all of the data is displayed perfectly (both the column headers and the data underneath the headers).

      I don't understand why when I initially load the page, the CSV file for the Interactive Grid does not download correctly, but after I click the submit button the CSV file does download correctly.

       

      Does anyone have any ideas?

       

      Thank you in advance.

        • 1. Re: Oracle Apex - Download CSV produces an empty report
          fac586

          KatReed wrote:

           

          I am currently using Oracle Apex version 18.2.0.00.12 on Internet Explorer on a computer running Windows 10.

          I am having a problem with downloading an Interactive Grid as a CSV file.

          On my Apex page I have an interactive grid and three page items. Two of the page items, P200_DATE_FROM and P200_DATE_TO, are Date Picker items. The other item is a button named "Submit" and its action is "Submit Page".

          This is the query for my interactive grid:

          1. select * from kat_test
          2. where repairdate between :P200_DATE_FROM and :P200_DATE_TO;

           

          Firstly, this won't work properly. All APEX session state items are stored as VARCHAR2 values (or CLOBs if they exceed 32KB in length). Therefore they must be explicitly converted to NUMBER or DATE types when compared to values of these types:

           

          select * from kat_test
          where repairdate between to_date(:P200_DATE_FROM, '<P200_DATE_FROM format>') and to_date(:P200_DATE_TO, '<P200_DATE_TO format>')
          

           

          There are four columns in the Interactive Grid:

          Issue - Varchar2

          Month - Number

          Week - Number

          Repairdate - Date

           

          The only settings I changed in the Interactive Grid are the title and the SQL query.

           

          I changed the Source of P200_DATE_FROM to PL/SQL Expression that returns the first day of the current month. It is set to be used "Only when the current value in session state is null". The expression is:

          1. trunc(last_day(sysdate)-1,'mm')

          I changed the Source of P200_DATE_TO PL/SQL Expression that returns the current date. It is also set to be used "Only when the current value in session state is null". The expression is:

          1. sysdate

          When I run the page, it loads exactly as expected in the sense that the P200_DATE_FROM page item is populated with the first date of the current month, the P200_DATE_TO item is populated with the current date, and the interactive grid displays the correct data.

          The problem: When I select Actions -> Download -> CSV, the CSV file downloads and opens as an Excel file, BUT only the header names are displayed. The cells underneath the headers are all empty. However, if I change one of the dates (or both) in the P200_DATE_FROM page item and/or the P200_DATE_TO page item and then press the submit button, the page is submitted and then when I click Actions -> Download -> CSV, the Excel file opens and all of the data is displayed perfectly (both the column headers and the data underneath the headers).

          I don't understand why when I initially load the page, the CSV file for the Interactive Grid does not download correctly, but after I click the submit button the CSV file does download correctly.

          Page item source and default values are not saved into persistent session state during page show processing, only set in the rendered page source. This means that they cannot be referenced in region source code. In order to do so, remove the item source settings and set the values in computations sequenced to execute before the IG region and the page items are rendered.

          • 2. Re: Oracle Apex - Download CSV produces an empty report
            ascheffer

            Or you can change the query for the IG to use the same defaults:

            select * from kat_test
            where repairdate between coalesce( to_date(:P200_DATE_FROM, '<P200_DATE_FROM format>'), trunc(sysdate,'mm') )
                                and coalesce( to_date(:P200_DATE_TO, '<P200_DATE_TO format>') , sysdate )
            
            • 3. Re: Oracle Apex - Download CSV produces an empty report
              KatReed

              fac586, thank you for all of your advice. I did not know that all APEX session state items are stored as VARCHAR2 values. I will change my query and keep that in mind for the future. I had actually tried before posting this question setting the values of the page items in computations sequenced to execute before the IG region and the page items are rendered, but, unless I did it wrong, when I changed the date in either page item using the date picker and submitted the page, the computed values were displayed again in the field after the page was submitted, as opposed to the new date that I chose. I actually learned in a different forum that all I need to do is to put the name of these items in the field "Items to submit" in the Source section of the IG underneath the query and it works perfectly.

              • 4. Re: Oracle Apex - Download CSV produces an empty report
                KatReed

                @ascheffer, thank you for your idea! Someone on a different forum actually suggested an idea similar to that, and it definitely works! I also learned that another solution is to put the name of these items in the field "Items to submit" in the Source section of the IG underneath the query and it works perfectly.

                • 5. Re: Oracle Apex - Download CSV produces an empty report
                  fac586

                  KatReed wrote:

                   

                  fac586, thank you for all of your advice. I did not know that all APEX session state items are stored as VARCHAR2 values. I will change my query and keep that in mind for the future. I had actually tried before posting this question setting the values of the page items in computations sequenced to execute before the IG region and the page items are rendered, but, unless I did it wrong, when I changed the date in either page item using the date picker and submitted the page, the computed values were displayed again in the field after the page was submitted, as opposed to the new date that I chose.

                  The computations should be conditional on the items being null. Alternatively, when a number of parameter values are involved, set them in a Once Per Session or When Reset Pre-Rendering process rather than using multiple conditions.