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