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

-- 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:

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

  -- 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:

  @pkg_apex_report.pks
  @pkg_apex_report.pkb

3- Create Interactive Report region

  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:

  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.

//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:

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

  $('#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:

select *
from emp
connect by level <= 3

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

  $('#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

  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

  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

  SELECT *
  FROM   emp

Step 3: Javascript


Create a HTML region and add the following javascript code:

  // 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;});
  });