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".
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.
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).
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 ...
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.
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.
<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)