APEX Interactive Reports - Customize Wait Display Part 2

APEX Interactive Reports - Custom Wait Display Part 2

Note: If you're using APEX 4.0 please see this post: http://www.talkapex.com/2010/08/apex-40-interactive-reports-customize.html

A few weeks ago I wrote about customizing the default APEX IR wait logo: http://apex-smb.blogspot.com/2009/04/apex-interactive-reports-customize-wait.html. Here is the second part of that article which focuses on overwriting APEX's dispIRBusyGraphics function.

Overriding the default "Busy Graphic" function can be used to customize messages/images as well as preventing users from resubmitting IR request which may take a long time.

An example of this can be found here

- 1: Create IR Report

1 2 3 4 5 6

-- Trying to make this a slow query to demonstrate the IR loader SELECT     e.*, sum(e.sal) over() test       FROM emp e CONNECT BY LEVEL <= 5

- 2: Download jQuery and Simple Modal and upload the JavaScript files in the Application's Static Files:

jQuery: http://jquery.com/
Simple Modal: http://www.ericmmartin.com/projects/simplemodal/

- 3: Create an HTML region and add the following:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script> <script src="#APP_IMAGES#jquery.simplemodal-1.2.3.js" type="text/javascript"></script> <script type="text/javascript"> /**  * @param pRegionStaticId ID of region to set to modal  * @param pOptions Options for modal Screen. See: http://www.ericmmartin.com/projects/simplemodal/#options for more info  */ goModal=function(pRegionStaticId, pOptions){   var vDefaults = {persist: true, overlayCss: {backgroundColor: '#606060'}}; // Note: It's important that you leave the persist = true otherwise items values will be cleared       pOptions = jQuery.extend(true,vDefaults, pOptions);       // To maintain order of APEX items (see forum posting above   $('#' + pRegionStaticId).wrap('<div></div>');       // Make sure the region is visible   $('#' + pRegionStaticId).show();       // Open Modal Screen   $('#' + pRegionStaticId).modal(pOptions);  }// goModal   /**  * Closes the modal screen  */ modalClose=function(){   $.modal.close(); }// modalClose   // OnLoad tasks $(document).ready(function(){   // Only apply if IR are present for page   if ($('.apexir_WORKSHEET_DATA').length > 0) {     // See apex_ns_3_1.js for _BusyGraphic     function dispIRBusyGraphics(pState){       if(pState == 1){         // Here apexir_LOADER is the object ID. You can use your own region if you wanted to etc...      goModal('apexir_LOADER', {position:['30%',]});     }     else{           modalClose();       }     return;     }// dispIRBusyGraphics           function updateIRJS(){      // This time out is required since after the report is refreshed via AJAX, need to reattach the l_LastFunction command      setTimeout(        function(){         gReport._BusyGraphic = function(pState){dispIRBusyGraphics(pState);};        },        1000       );     }           gReport = new apex.worksheet.ws('');     gReport.l_LastFunction = function(){dispIRColGroups();}     // Need to put timeout since not registering on initialization     setTimeout(function(){gReport._BusyGraphic = function(pState){dispIRBusyGraphics(pState);};},500);     updateIRJS();   } //If IR exist }); </script>

Presenting at COUG (Calgary Oracle User Group) May 28th

I'll be giving a presentation on APEX at the Calgary Oracle User Group (COUG) on May 28th at 8:00 am. For those of you in Calgary it will be at 17 B/C on the 17th floor of the Petro-Canada west tower (Corner of 1st and 6th ave).

Please see www.coug.ab.ca for more information.

I'll post a reminder a week before the presentation.

Custom Download for APEX Interactive Reports

A few months ago I needed to modify the downloaded Interactive Report (IR) to CSV function so that we could include group headings, report description, and report parameters (please see Column Groups in APEX Interactive Reports on how to display column groups in IR). Denes Kubicek has a package to download regular reports in Excel format, however it does not return the query that the user is currently viewing. This is really important since users may add columns, change the ordering of columns, etc. When users download a report it should reflect what is currently being displayed on the screen.

After digging through the APEX packages I was able to write some code to generate my own custom download function for IRs. Since the code is fairly long you'll need to get a copy of the code here (please save the file as pkg_apex_report.zip). Note: This code is NOT ready for a production environment!. It's also important to note that some of the grant privileges could have security implications and is not recommend to be run for public applications. If you need to use these grants in a production instance I suggest creating a special "APEX" schema and write some custom wrapper functions. Since grants are required from the APEX schema I can't post a working example of this application on apex.oracle.com.

Now that I'm done with my disclaimers here's how to customize IR downloads (don't forget to download the code). Please note that the schema I developed in this was called "giffy".

1- Grant Privileges

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

-- Change "apex_030200" to the version of APEX that you are using -- Change "giffy" to your schema GRANT EXECUTE ON apex_030200.wwv_flow_worksheet_standard TO giffy; CREATE OR REPLACE SYNONYM giffy.wwv_flow_worksheet_standard FOR apex_030200.wwv_flow_worksheet_standard   GRANT EXECUTE ON apex_030200.wwv_flow_conditions TO giffy; CREATE OR REPLACE SYNONYM giffy.wwv_flow_conditions FOR apex_030200.wwv_flow_conditions;   GRANT EXECUTE ON apex_030200.wwv_flow_worksheet TO giffy; CREATE OR REPLACE SYNONYM giffy.wwv_flow_worksheet FOR apex_030200.wwv_flow_worksheet;   GRANT EXECUTE ON apex_030200.wwv_flow_render_query TO giffy; CREATE OR REPLACE SYNONYM giffy.wwv_flow_render_query FOR apex_030200.wwv_flow_render_query;

2- Create Package apex_report


Download code here (save the file as pkg_apex_report.zip)
In SQL*Plus run:

1 2 3 4

@pkg_apex_report.pks @pkg_apex_report.pkb

3- Create Interactive Report region

1 2 3 4

SELECT *   FROM emp

4- Create "Download Page"
- Create a new HTML Page.
- Create a page item P3_BASE_REPORT_ID
- Create a page item P3_PAGE_ID

- Create a PL/SQL Process
- Point: On Load Before Header
- PL/SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

DECLARE   v_region_id apex_application_page_regions.region_id%TYPE;   v_base_report_id apex_application_page_ir_rpt.base_report_id%TYPE;   v_page_id apex_application_page_ir_rpt.page_id%TYPE; BEGIN   v_base_report_id := :p3_base_report_id;   v_page_id := :p3_page_id;     SELECT ir.region_id     INTO v_region_id     FROM apex_application_page_ir_rpt irr,          apex_application_page_ir ir    WHERE irr.application_id = :app_id      AND irr.page_id = v_page_id      AND irr.session_id = :app_session      AND irr.base_report_id = v_base_report_id      AND irr.interactive_report_id = ir.interactive_report_id;     pkg_apex_report.sp_download_report (p_page_id            => v_page_id,                                       p_region_id          => v_region_id,                                       p_base_report_id     => v_base_report_id,                                       p_format_typ         => 'CSV'                                      ); END;

5- Alter Interactive Report Download Link
Please see APEX Interactive Report - Download with One Click for more information about this code. You'll need jQuery for this.

1 2 3 4 5 6

//Note: "3" is my download page $(document).ready(function() {   $('.dhtmlSubMenuN[title="Download"]').attr('href','f?p=' + $v('pFlowId') + ':3:' + $v('pInstance') + '::NO:3:P3_PAGE_ID,P3_BASE_REPORT_ID:' + $v('pFlowStepId') + ',' + $v('apexir_REPORT_ID'));  });

At this point you should be able to run your interactive report and download the custom CSV file. You'll notice that there's a block of HTML that appears at the top of the CSV file. This is caused by the call (wwv_flow_worksheet.get_worksheet_report_query) to get the SQL the user is currently looking at. I don't have a fix or a work around (if you know of one please post in comment section)

Please post any bugs, suggestions, or updates to the code as I'd like to create a production version for this code.

APEX Interactive Reports - Customize Wait Display Part 1

We recently needed to change, and move, the default Interactive Report (IR) AJAX "Loading" image (spinning wheel):

After some investigation they're several things you can do depending on your requirements. A demo application is available here.

Part 2 is here: http://apex-smb.blogspot.com/2009/04/apex-interactive-reports-customize-wait_28.html

Before we go over the solution it's important to review where the image is located and how it gets toggled on and off. The image is stored in a span region which is hidden by default. The code looks like this:

1 2 3 4 5

<span style="" id="apexir_LOADER">   <img src="/i/ws/ajax-loader.gif"> </span>

If you want to manually make it visible you can run the following code:

1 2 3

$('#apexir_LOADER').show();

The JS code to toggle the image is stored in: apex_ns_3_1.js. You can view an uncompressed version of this file in your installation directory: apex_3.1.2\apex\images\javascript\uncompressed\apex_ns_3_1.js To toggle the image, APEX calls the following JS function: apex.worksheet.ws._BusyGraphic (We won't be using this function in this post but I'll do another posting on how we can use it for a completely customizable solution)

First Let's build a IR page with the emp table:

1 2 3 4 5

select * from emp connect by level <= 3

The easiest option is if all you need to do is change the image:

1 2 3

$('#apexir_LOADER img')[0].src="#IMAGE_PREFIX#processing3.gif";

The new image, processing3.gif, is part of APEX as well. You can reference your own images if you'd like.

In Part 2 I'll go over changing the actual function that shows and hides the loading image. This will give you complete control of the loading message.

Presenting at ODTUG Kaleidoscope 2009 - APEX

Frank Hoogendoorn and I will be presenting at the Application Express Symposium on Sunday at ODTUG Kaleidoscope 2009. Our presentation is called "Getting the Ape out of APEX".

I attended ODTUG last year in New Orleans and was amazed with all the excellent APEX presentations, I'm sure this year will be even better. If you can only attend once conference this year and you are using, or interested in, APEX this is the conference to go to!

You can go to www.odtugkaleidoscope.com for more information and registration.

See you then.

Log APEX Interactive Report Search Filter

A few days ago someone posted a question on the forums http://forums.oracle.com/forums/message.jspa?messageID=3365707 asking how to log the search filter in an IR. Here's how to do it. A working example is available here.


Please note you will need jQuery for this example


Step 1: Create Log Table

1 2 3 4 5 6

CREATE TABLE tir_search_filter_log(   search_filter VARCHAR2(255) NULL,   search_date DATE DEFAULT SYSDATE NOT NULL,   username VARCHAR2(255) NOT NULL);

Step 2: Create Application Process


Create an On Demand Application Process. Call it: AP_LOG_SEARCH_FILTER

1 2 3 4 5 6 7 8 9 10 11 12 13

BEGIN   INSERT INTO tir_search_filter_log               (search_filter,                search_date,                username               )   VALUES      (apex_application.g_x01,                SYSDATE,                :app_user               ); END;

Step 3: Create Interactive Report


Create a IR Page

1 2 3 4

SELECT * FROM   emp

Step 3: Javascript


Create a HTML region and add the following javascript code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

// Function to call our Application Process to log the search function fLogSearch(){   var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=AP_LOG_SEARCH_FILTER',$v('pFlowStepId'));      get.addParam('x01',$v('apexir_SEARCH')); // IR Search Filter Value   gReturn = get.get();  // Call AP Log Search Filter to insert log   // Call APEX IR Search   gReport.search('SEARCH'); }   // Run the following once the document is ready $(document).ready(function(){    // -- Handle Go Button --   // Unbind all events. Important for order of execution   $('input[type="button"][value="Go"]').attr('onclick',''); //unbind click event   // Rebind events   $('input[type="button"][value="Go"]').click(function(){fLogSearch()});       // -- Handle "Enter" in input field --   $('#apexir_SEARCH').attr('onkeyup',''); //unbind onkeyup event   // Rebind Events   $('#apexir_SEARCH').keyup(function(event){($f_Enter(event))?fLogSearch():null;}); });