APEX: Excel/html download for interactive Reports

Purpose

With this package you can download your interactive reports in Excel or html format.

You can evaluate the number of total rows selected (SQL count) in IR.
  It is helpful only for large tables (with more than "max_row_count" rows).

With the help of this package, you can implement this functionality for all your interactive reports in 4-6 hours,
  without the need of change individual interactive reports.

Limits and troubles:

  • Undocumented features of Apex and MS-Excel are used too.
  • Additional grants are needed for Apex tables(2) and functions(1).
  • The package was tested only with:
    • Apex 3.2.1.00.10
    • Excel-2003 (Excel download)
    • Internet Explorer and Mozilla (html download)
    • Language: hu
  • Aggregate function median will not be evaluated.

Download and install

Download and unpack the package APEX_IR_HTML_EXCEL.

Change (except Hungary) the "Excel header settings". They are at the top of APEX_IR_HTML_EXCEL.pkb.
If not changed, the printed Excel header and footer line will be unusable.

Create (sys as sysdba) the following synonyms and grants.
(SCHEMA is the name of your application schema, APEX_030200 is your Apex instance.)

-- execute as "sys as sysdba"
create synonym SCHEMA.WWV_FLOW_WORKSHEET_COMPUTATION for APEX_030200.WWV_FLOW_WORKSHEET_COMPUTATION;
grant select on APEX_030200.WWV_FLOW_WORKSHEET_COMPUTATION to SCHEMA;
create synonym SCHEMA.WWV_FLOW_WORKSHEET_COLUMNS for APEX_030200.WWV_FLOW_WORKSHEET_COLUMNS;
grant select on APEX_030200.WWV_FLOW_WORKSHEET_COLUMNS to SCHEMA;
create synonym SCHEMA.WWV_FLOW_CONDITIONS for APEX_030200.WWV_FLOW_CONDITIONS;
grant execute on APEX_030200.WWV_FLOW_CONDITIONS to SCHEMA;
Only with this grants you can:
  • found the IR computations data,
  • evaluate the "display conditions" for the fields.

Compile the package.

Usage of the package - fast test of Excel download

Create the following application items:
  • F_APEXIR_REPORT_ID
  • F_REQUEST

Create an application process as shown below:

For copy+paste:
  Name: DOWNLOAD_HTML_EXCEL_IR
  Process Text: APEX_IR_HTML_EXCEL.download('E',:F_APEXIR_REPORT_ID,:F_REQUEST);
  Expression 1: DOWNLOAD_HTML_EXCEL_IR

Insert following Javascript into your IR Region Template:

<script type="text/javascript">
document.onkeydown=function(e){
 var e=window.event || e;
 if (e.keyCode == 121)
  { popUp2('f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.:DOWNLOAD_HTML_EXCEL_IR:::F_REQUEST,F_APEXIR_REPORT_ID:&REQUEST.,'
            +$v('apexir_REPORT_ID'),760,220);
  }
}
</script>

You can download now with functionskey(keyboard) F10 your interactive reports in Excel format.

Do not test with large tables, because all selected table lines are downloaded! (See next paragraph, parameter nr 6.)

Example EN   Example GE   Example HU   Open with MS-Excel, see also the "print preview".

Example in pictures

Usage of the package - fast test of all download parameters

Parameters of APEX_IR_HTML_EXCEL.download:
  P_HTML_EXCEL       in varchar2,                        -- E:excel H:html
  P_APEXIR_REPORT_ID in number,                          -- APEX_APPLICATION_PAGE_IR_RPT.base_report_id
  P_REQUEST          in varchar2,                        -- Original REQUEST value of the IR Page
  P_ONLY_TOTALS      in varchar2 default 'N',            -- N/Y  Y: only break fields and aggregations displayed
  P_HTML_WRAP        in varchar2 default 'nowrap',       -- normal/nowrap
  P_MAX_ROWS         in number default 0,                -- -1: use APEX_APPLICATION_PAGE_IR.max_row_count
                                                         -- 0: not limited, otherwise use this value
                                                         -- -2: not limited for 'only totals',
                                                         --     APEX_APPLICATION_PAGE_IR.max_row_count otherwise
  P_APP_ID           in number default v('APP_ID'),      -- Application_id
  P_APP_PAGE_ID      in number default v('APP_PAGE_ID'), -- Original APP_PAGE_ID value of the IR Page
  P_SESSION          in number default v('SESSION'));    -- Session_id

Turn the 'E' into 'H' in process text of the application process DOWNLOAD_HTML_EXCEL_IR:

APEX_IR_HTML_EXCEL.download('H',:F_APEXIR_REPORT_ID,:F_REQUEST);
The download format is html.
  • Internet Explorer:
    • The header line remains fix on top of the browser window.
    • You have two scrollbars. Left for table lines, right for the whole page.
      (Above the header line are: page title, filters, searches and highlights.)
  • Mozilla and IE: The header and a footer (sysdate+user) lines appear on all printed pages.
Example   Example as picture   Printed page example as picture

Turn the 'H' into 'E' again, and give an additional parameter as 'Y':

APEX_IR_HTML_EXCEL.download('E',:F_APEXIR_REPORT_ID,:F_REQUEST,'Y');
The download format is again Excel. You can download only IR breaks with aggregates (if any). Try with breaks and/or aggregates.
This (4.) parameter is "only totals": 'Y/N' (default: 'N').
Example EN   Example GE   Example HU   Example as picture

Turn the 'E' into 'H' again, parameter 4 into 'N', and give the parameter nr. 5 as 'normal'.

APEX_IR_HTML_EXCEL.download('H',:F_APEXIR_REPORT_ID,:F_REQUEST,'N','normal');
The download format is html, all table lines (and aggregates) are downloaded, and "white-space: normal".
This means that the text wird wrap to fit into the window (when necessary and possible).
This (5.) parameter is "wrap_style": 'normal/nowrap' (default nowrap). Nowrap means: text will never wrap to the next line.
Example normal   Example nowrap  See the pages at window lessening.      Example as picture

Parameter nr 6 in APEX_IR_HTML_EXCEL.download is a number: "max. SQL rows to process".

  • When -1, then the IR field "Maximum Row Count" will be used.
    Remark: the rows in download are not always the same as displayed in IR.
    This is because the "download SQL" does not always process the rows in the same order as the "Apex IR SQL".
  • When 0, then all selected table rows are download.
  • When greater than 0, then (max) so many table rows are download.
  • When -2, then:
    • For detail-report (param. 4, only_totals='N') the IR field "Maximum Row Count" will be used.
    • For breaks+aggregates-report (param. 4, only_totals='Y') is no limit (all table rows are aggregated).
Default is 0 (i.e. no download limit).

Other features and options of IR download

You can change the html/Excel fontsize and colors. They are at the top of APEX_IR_HTML_EXCEL.pkb.

The download file name is the IR page name, converted into 'US7ASCII'.

You can start the download on a different page (from the IR page).
The original (IR page) REQUEST and APP_PAGE_ID values are used in the

  • Request ...
  • Current Page ...
display conditions. (They are download parameters.)

When you will omit report columns from download, you can indicate this with column display condition "Request = Expression 1":

  • If "Expression 1" = DOWNLOAD_HTML_EXCEL_IR, the column is omitted from html/Excel download.
  • If "Expression 1" = DOWNLOAD_HTML_IR, the column is omitted from html download.
  • If "Expression 1" = DOWNLOAD_EXCEL_IR, the column is omitted from Excel download.
All other display conditions with "Request" will be usual evaluated.

Usage of the package - fast test of SQL count

Create an "on demand" application process as shown below:

Process text (for copy+paste):

declare
  l_ir_query  varchar2(32767);
  l_sql_count varchar2(200);
begin
  l_ir_query := APEX_IR_HTML_EXCEL.ir_query(:F_APEXIR_REPORT_ID,
                :F_REQUEST,:APP_ID,:APP_PAGE_ID,:APP_SESSION);
  if substr(l_ir_query,1,7)='Error: ' then
    HTP.print('Error in "Number of Lines" create SQL');
   else
    execute immediate 'select count(*) from (' ||
                       l_ir_query || ')' into l_sql_count;
    l_sql_count := trim(to_char(l_sql_count,'999G999G999'));
    HTP.print('<center>Number of rows: <b>'||l_sql_count||'</b>');
  end if;
 exception
  when others then HTP.print('Error in "Number of Lines"');
end;

Insert following (bold) Javascript lines into your IR Region Template:

<script type="text/javascript">
document.onkeydown=function(e){
 var e=window.event || e;
 if (e.keyCode == 119)
  { window.open('f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.:APPLICATION_PROCESS=SQL_COUNT_IR:::' +
                'F_REQUEST,F_APEXIR_REPORT_ID:&REQUEST.,'+$v('apexir_REPORT_ID'),'popup_SQL_COUNT',
                'width=220, height=60, left=400, top=300, toolbar=0,location=0,' +
                'directoryes=0, menubar=0, scrollbars=0, status=0, resizable=1');
  }
 if (e.keyCode == 121)
  { popUp2('f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.:DOWNLOAD_HTML_EXCEL_IR:::F_REQUEST,F_APEXIR_REPORT_ID:&REQUEST.,'
            +$v('apexir_REPORT_ID'),760,220);
  }
}
</script>

You can see (in a little popup window) with functionskey(keyboard) F8 an SQL row count for your interactive reports.

You see, that the package function APEX_IR_HTML_EXCEL.ir_query returns an SQL query string for the corresponding IR.

Parameters of APEX_IR_HTML_EXCEL.ir_query:
  P_APEXIR_REPORT_ID in number,                          -- APEX_APPLICATION_PAGE_IR_RPT.base_report_id
  P_REQUEST          in varchar2,                        -- Original REQUEST value of the IR Page
  P_APP_ID           in number default v('APP_ID'),      -- Application_id
  P_APP_PAGE_ID      in number default v('APP_PAGE_ID'), -- Original APP_PAGE_ID value of the IR Page
  P_SESSION          in number default v('SESSION'));    -- Session_id

Other package functions

The function APEX_IR_HTML_EXCEL.only_totals_count return the number of report columns for the "only totals = 'Y'" download.
Parameters of APEX_IR_HTML_EXCEL.only_totals_count:
  P_APEXIR_REPORT_ID in number,                          -- APEX_APPLICATION_PAGE_IR_RPT.base_report_id
  P_REQUEST          in varchar2,                        -- Original REQUEST value of the IR Page
  P_APP_PAGE_ID      in number default v('APP_PAGE_ID'), -- Original APP_PAGE_ID value of the IR Page
  P_APP_ID           in number default v('APP_ID'),      -- Application_id
  P_SESSION          in number default v('SESSION'));    -- Session_id
With this function you can test, whether an "only totals" download is possible.

The package function APEX_IR_HTML_EXCEL.apex_lang_message is used in SQL-s of the package.
Reason: the function apex_lang.message(P_NAME) returns P_NAME (instead of null), when P_NAME undefined (as message).

Other Solutions

You can use the package according to your ideas. For example instead of functionskeys you can use:
  • "Page 0" buttons and items.
  • Page buttons and items.
  • Navigation bar entries.

You can here download a full solution. This is an application to import (in UTF-8 format, APP_ID = 2010):

  • Functionskey F8 shows an sql-count on page 8.
  • With functionskey F10 you can choose html/Excel and details/totals (on page 10).
  • The functionskey F9 turns into the "filter".
  • The download parameter P_MAX_ROWS is -1 (fix in the application process: DOWNLOAD_HTML_EXCEL_IR).
  • The parameter P_HTML_WRAP is derived from a navigation bar entry. This wrap/nowrap entry is used on all pages.
  • IR pages are all unchanged.
Javascript lines in IR Region Template:
<script type="text/javascript">
document.onkeydown=function(e){
 var e=window.event || e;
 if (e.keyCode == 119)
  { popUp2('f?p=&APP_ID.:8:&SESSION.:&APP_PAGE_ID.:::F_REQUEST,F_APEXIR_REPORT_ID:&REQUEST.,'
            +$v('apexir_REPORT_ID'),760,220);
  }
 if (e.keyCode == 120)
  { gReport.controls.filter();
  }
 if (e.keyCode == 121)
  { popUp2('f?p=&APP_ID.:10:&SESSION.:F10:::F_REQUEST,F_APEXIR_REPORT_ID,P10_APP_PAGE_ID:&REQUEST.,'
            +$v('apexir_REPORT_ID')+',&APP_PAGE_ID.',760,220);
  }
}
</script>

You can copy pages (8 and/or 10) or details (appl. process, javascript) into your application.
Remark: The form in the template of the "popUp2" page should be open. End of "Header":

...
</head>
<body #ONLOAD#>#FORM_OPEN#

For testing the application you should have a table (COUNTRY).
You can download this table (as textfile in UTF-8 format):

With Apex you can load the table:

On page 1 you can create the computed(calculated) field:

  Albert Csizmazia
  Eötvös Loránd University, Budapest, Hungary
  csa@elte.hu  (hungarian, deutsch, english)