Column Groups in APEX Interactive Reports

Note: If you are using APEX 4.0 I've developed a plugin for this: http://www.talkapex.com/2010/12/column-groups-in-apex-40-interactive.html

A while ago Dimitri Gielis helped us to display column groups in Interactive Reports (IR) in APEX. Please see his blog posting to get a background of how to enable column groups in Interactive Reports.



After some testing we've made several changes to the code. The updated code fixes a bug that would display multiple group headers. It does not load the group headers via AJAX so it speeds up response time. I've included the updated code below. The example of this is here.

Please note you will need jQuery for this example

Step 1: Application Process
Create an Application Process to load the IR colum group header information
Name: AP_GET_IR_COL_GROUPS
Point: On Load: After Header (page template header)

DECLARE
  v_sql                         VARCHAR2 (500);
  v_count                       PLS_INTEGER;
BEGIN
  -- Need to ensure at least 1 col group exists
  SELECT COUNT (column_group_id)
  INTO   v_count
  FROM   apex_application_page_ir ir, apex_application_page_ir_col c
  WHERE  ir.application_id = :app_id
  AND    ir.page_id = :app_page_id   
  AND    ir.interactive_report_id = c.interactive_report_id;

-- Need to do join columns to IR rather than the page_id since columns that are added after IR was created need have a null page_id
  v_sql                      := '
SELECT c.column_alias,
       c.report_label,
       c.column_group
FROM   apex_application_page_ir_col c,
       apex_application_page_ir i
WHERE  i.application_id = :app_id
AND    i.page_id = :app_page_id
AND    i.interactive_report_id = c.interactive_report_id
AND    c.column_group IS NOT NULL';
  HTP.p ('');
END;

Step 2: Javascript Code
You can put this on Page 0 or just directly on the page with the Interactive Report

dispIRColGrpHeader=function(){
  if(typeof(gPageIRColGrps) != "undefined"){
    // retrieve the Interactive report table
    var vTbl = $('.apexir_WORKSHEET_DATA');

    // change the look and feel of the IR table 
    $(vTbl).attr("border","1");

    // Prevent Duplicate rows
    $('#irColGrpRow').remove();

    // Add the Column Group row
    $(vTbl[0].rows[0]).before('<tr id="irColGrpRow"></tr>');

    var vPrevColGrp = '';
    var vColGrpExists = false;
    var vColSpan = 1;
    // Loop over the row headers and see if we need to add a column group.
    for (var i = 0; i < $(vTbl[0].rows[1].cells).length; i++){
      // For IR, the column headers have divs with id of apexir
      vColId = '';
      // Only set the col ID if it exists (needed for IR row_id icon)
      if (typeof($('.apexir_WORKSHEET_DATA tr:eq(1) th:eq(' + i + ') div').attr('id')) != "undefined")
        vColId = $('.apexir_WORKSHEET_DATA tr:eq(1) th:eq(' + i + ') div').attr('id').replace(/apexir_/, '').toUpperCase();
      var vFoundColGrp = false; // This column has an associated column grp
      var vColGrp = ''; // Current Column group
      
      // Find the ID in the IR Groups global variable (genereated in AP)
      for (var j = 0; j < gPageIRColGrps.row.length; j ++ ){
        if (gPageIRColGrps.row[j].COLUMN_ALIAS.toUpperCase() == vColId) {
          vFoundColGrp = true;
          vColGrpExists = true;
          vColGrp = gPageIRColGrps.row[j].COLUMN_GROUP;
          break;
        }//if
      }// For IR Col Groups
      
      // Only print the col group header for the previous entry. This allows us to set the col span for similar groups
      // Have to do it this way to support IE  (otherwise we could look at the previous entry and update it's col span
      
      // If the current 
      if (vColGrp.length > 0 && vColGrp == vPrevColGrp){
        // Don't display the 
        vColSpan = vColSpan + 1;
      }
      else if(i > 0) {
        // Display the previous item
        $('#irColGrpRow').append('<th colspan="' + vColSpan + '">' + vPrevColGrp + '</th>');
        vColSpan = 1;
      }
      
      // If this is the last item then display it
      if (i == $(vTbl[0].rows[1].cells).length-1) {
        $('#irColGrpRow').append('<th colspan="' + vColSpan + '">' + vColGrp + '</th>');
      }
      
      vPrevColGrp = vColGrp;
    }// For each column being displayed

    // Remove the col group heading if no column groups:
    if (!vColGrpExists)
        $('#irColGrpRow').remove();
  } // Column groups exist
  
} // dispIRColGrpHeader

// Once the page is finsihed loading run the following (which will activate the column grouping display).
$(document).ready(function(){

// If page has an IR then set up for column grouping
  if ($('.apexir_WORKSHEET_DATA').length > 0) {
    
    function dispIRColGroups(){
      // Set the class for div hover since can't do in IE
      $('table.apexir_WORKSHEET_DATA th div').mouseover(function(){
        $(this).addClass('apexir_WORKSHEET_DATA_th_div_hover');
      });
      $('table.apexir_WORKSHEET_DATA th div').mouseout(function(){
        $(this).removeClass('apexir_WORKSHEET_DATA_th_div_hover');
      });  
    
     dispIRColGrpHeader();
     // This time out is required since after the report is refreshed via AJAX, need to reattach the l_LastFunction command
     setTimeout(
       function(){
        gReport.l_LastFunction = function(){dispIRColGroups();};
       },
       1000
      );
    }
      
    gReport = new apex.worksheet.ws('');
    gReport.l_LastFunction = function(){dispIRColGroups();}
    dispIRColGroups();
  }//if
});    

APEX Interactive Report - Download with One Click

We've been working with Interactive Reports (IR) over the past few months and have had to do a lot of customization to meet some of our customers requirements. Over the next few weeks my posts will be focused on these customizations.

To start, here's an example of how to modify the download button in IRs. Just some background on this, we're only supporting CSV downloads now. When the clients would download IRs they would have to click the "Download" link then click on the "CSV" button.

We found this extra step unnecessary so here's a way on how to do it in one step so that it will download as soon as they click "Download". We used jQuery to help out a bit. Here's a working example.

$(document).ready(function() {
  $('.dhtmlSubMenuN[title="Download"]').attr('href','f?p=' + $v('pFlowId') + ':' + $v('pFlowStepId') + ':' + $v('pInstance') + ':CSV:');  
});

Here's a detailed summary of how it works: If you scroll over the "CSV" download button you'll noticed that it looks something like : "f?p=102:3:207941080701500:CSV:". If we break that down it's really: "f?p=:::CSV:". In Javascript you can reference each as follows:

APP_ID = $v('pFlowId');
APP_PAGE_ID = $v('pFlowStepId');
SESSION_ID = $v('pInstance');

Now to alter the "Download" link from the interactive report menu we use jQuery to identify the link: "$('.dhtmlSubMenuN[title="Download"]')" and then use the attr function to set with the new URL.

Custom Authentication Status

When writing custom authentication scheme I noticed that invalid logins weren't showing up properly in the apex logs (apex_workspace_access_log).

Here's how to re-create the problem along with the fix.

First let's create a custom authentication function:

CREATE OR REPLACE FUNCTION blog_cust_auth (
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
  RETURN BOOLEAN
AS
BEGIN
  IF     LOWER (p_username) = 'pass'
     AND LOWER (p_password) = '123' THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

Let's set the APEX authentication scheme to use this custom function:

Shared Components / Authentication Schemes:
- Create
- From Scratch
- Name: BLOG_TEST
- Keep hitting "Next" until:
- Credentials Verification Method:
- return blog_cust_auth
- Keep hitting "Next" until the end

- Change the current authentication scheme to BLOG_TEST
- On the Authentication Schemes page, on the right hand side select "Change Current" and select "BLOG_TEST'

Let's create a region to always display the authentication statuses:
- Create Page
- Page Zero
- On Page 0 Create a region
- Report

SELECT   user_name,
         authentication_method,
         access_date,
         authentication_result,
         custom_status_text
    FROM apex_workspace_access_log
   WHERE application_id = :APP_ID
ORDER BY access_date DESC

Now let's try to log in using a valid username and password:
- username: pass, password: 123

If things worked well you're now on P1, and the "AUTHENITCATION_RESULT" is flagged as "AUTH_SUCCESS"



Now logout and try to login with invalid credentials
- username: pass, password: fail

You'll notice that the login failed, however it still logged it as a successful login attempt:



Let's update our authentication function to use the 2 authentication result customizations:
apex_util.set_authentication_result and apex_util.set_custom_auth_status

CREATE OR REPLACE FUNCTION blog_cust_auth (
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
  RETURN BOOLEAN
AS
BEGIN
  IF     LOWER (p_username) = 'pass'
     AND LOWER (p_password) = '123' THEN
    RETURN TRUE;
  ELSE
    -- Set APEX authentication Codes
    apex_util.set_authentication_result (p_code => 3);
    -- Set our own APEX custom text
    apex_util.set_custom_auth_status (p_status => 'Bad Username');
    RETURN FALSE;
  END IF;
END;

Let's now see what happens when we now login with invalid credentials:



Notice that we now have proper error information in the logs? Here's a list of some codes you can use for apex_util.set_authentication_result, taken from the view apex_workspace_access_log:

0, 'AUTH_SUCCESS',
1, 'AUTH_UNKNOWN_USER',
2, 'AUTH_ACCOUNT_LOCKED',
3, 'AUTH_ACCOUNT_EXPIRED',
4, 'AUTH_PASSWORD_INCORRECT',
5, 'AUTH_PASSWORD_FIRST_USE',
6, 'AUTH_ATTEMPTS_EXCEEDED',
7, 'AUTH_INTERNAL_ERROR',

Of course you can use your own codes but I'd suggest sticking with these codes to utilize the APEX views. You can supplement the codes with the custom authentication status text (apex_util.set_custom_auth_status)

"Invalid set of rows requested, the source data of the report has been modified." - Explanation

Hi,

When I first started using APEX I occasionally got the following pagination error message:

"Invalid set of rows requested, the source data of the report has been modified.
reset pagination"

At first I had no clue what was causing the error but afte a while I finally figured it out. The best way to describe the error is to see exactly what is happening. Full example is here

- Create a report:

SELECT *
  FROM emp
 WHERE UPPER (ename) LIKE '%' || UPPER (:P800_LETTER) || '%'

When creating the report, change the number of rows from 15 to 5 for this example. This is important for this demonstration!

Now create a page item called P800_LETTER and a submit button who submits and redirect to P800

If things went well you'd have a report that looks like this:



To illustrate the problem click on the "Next" pagination link so that we are seeing rows 6~10.
Now, in the text box enter the letter "I" (this will restrict to 4 rows) and click submit. You should see the following error message:



To summarize what just happened, you initially requested a report with 14 rows and told APEX to display 5 at a time. When you clicked the "Next" pagination button you told APEX for the report you're currently looking at display rows 6~10. So now, if you were to refresh the page APEX would continue to show rows 6~10. When you filtered the report, by restricting to employees with the letter "I" in their name, you get a result with only 4 rows however APEX is still trying to display rows 6~10. I hope you now see the problem with this.

So now that we've demonstrated the problem, how do you fix it? Well there's several ways. To solve our current issue, on P800 in the submit branch we could check the "reset pagination for this page". This means that each time you submit the page APEX will set the display rows back to 1~5.

There are also other ways to reset the pagination automatically. You can reset pagination in the URL: In the "Clear Cache" section you can enter "RP" to clear the page's cache. Please see the APEX documentation for more information. There is also a page process to reset the pagination. This allows you to also set the scope of the current page, multiple pages, or all pages.

I know the issue has come up to reset the pagination for an individual report rather than the entire page. I currently don't know of any method, however if you do please post!

q Function: Escape Single Quotes

Last year Scott Spendolini gave our company an excellent 3 day training session on APEX. He also showed us a very nifty function (new in 10g I think) to help not having to escape single quotes when defining strings. This is not APEX specific, however it will help you if you ever have to write a block of PL/SQL to return an SQL statement for reports.

Instead of writing out a long description here's an example:

DECLARE
  v_sql VARCHAR2 (255);
  v_result VARCHAR2 (255);
BEGIN
  v_sql := 'select ''hello'' into :a from dual';

  EXECUTE IMMEDIATE v_sql
               INTO v_result;

  DBMS_OUTPUT.put_line (v_result);
END;

Notice how I had to put 2 single quotes around "Hello" to escape the single quote characters?

Now using the q function I don't need to do that:

DECLARE
  v_sql VARCHAR2 (255);
  v_result varchar2(255);
BEGIN
  v_sql := q'!select 'hello' into :a from dual !';

  EXECUTE IMMEDIATE v_sql
               INTO v_result;

  DBMS_OUTPUT.put_line (v_result);
END;

Notice now how "Hello" is wrapped as it would appear if it were not in variable definition function?

This can save you a lot of time by avoiding having to escape single quotes in strings!

Update:  q Function Inside a q Function

Test Data in Production Applications

Hi,

Well this isn't an APEX specific post but I thought it should be mentioned either way. It raises a good point about keeping test data out of production.

I got the following email from a colleague today:

"I’m a bit of a golf fan, and I like to read up on current events, standings, player profiles, etc. My favorite site to do this is www.pgatour.com

I was recently searching for a player with the surname of Fischer on the website. I had a good laugh when the search results came back. Take a look at the 6th name in the list in the attached image…"

Here's the screenshot:



- Martin