Wednesday, March 25, 2009

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

38 comments:

  1. Thanks for the update. This is pretty cool stuff.

    ReplyDelete
  2. Cool stuff. Is this also possible for regular reports?

    ReplyDelete
  3. Hi Maurice,

    You could do this for regular reports, however you'd need some sort of mechanism to associate column groups with the columns.

    ReplyDelete
  4. Martin,

    If you could, send me an email at dmcghan@gmail.com... I'm curious about your code.

    Regards,
    Dan

    ReplyDelete
  5. Hi,

    Is there a way to also code a function that would identify when an IR has been filtered so I could trigger a refresh on a different report using javascript?

    Thanks.

    ReplyDelete
  6. Hi,

    Yes you could probably do that. Do you want to trigger the report refresh when a filter is added via the column drop down, or when it is added via the IR tools menu?

    And just to clarify, you want to refresh another non IR?

    Martin

    ReplyDelete
  7. Hi,

    Yes i have a mapviewer on my page that displays the items on a map and an IR at the bottom that displays the items in a list. If the user filters the list (either with the Go button or the filter option; or again removes a filter or changes the enabled/disable etc), i want to update the mapviewer to now only show the filtered items. I was able from one of your other post to see an example where you altered the function of the Go and onkeyup to track the filter but it did not apply to the other filtering tools.

    ReplyDelete
  8. Hi,

    So you want to do the following when a filter is applied (either through the tools option or through the column drop down list):

    - Pass the filter values to your report region
    - Refresh another report region (or some JS call) and apply the filter values to it.

    Do you have an example on apex.oracle.com for this?

    Martin

    ReplyDelete
  9. Yes,

    I have already a JS function that loads the info in the map using jQuery and then loads the IR.

    I need to track the event in the browser when the IR is refreshed so that I can call the JS function that load the map.

    From your example, I was able to override the search function and at the end could reload the map but if the user does the filtering via the Filter option in the menu then it does not call the search function or when a filter is displayed and the user clicks on the icon to remove it, the report is refreshed but again Apex does not use the search function. I am trying to figure out which functions need to be overriden.

    I have not seen any example for this anywhere.

    Thanksé

    ReplyDelete
  10. Hi,

    You may need to dig around the APEX views for this one to find the filters that get applied to the users current IR. If you look at my code for downloading IR (http://apex-smb.blogspot.com/2009/04/custom-download-for-apex-interactive.html) I go over how you can find the current report that the user is looking at. With that information and with the APEX views you should be able to find the filters that the user has applied to their IR.

    If you're still having problems with this I can look into it some more in a few weeks.

    Martin

    ReplyDelete
  11. I think i have figured it out. Thanks again.

    ReplyDelete
  12. Martin, Is it possible for you to share the sample/example application. I have not been successful in make it work. Your help is appreciated.

    ReplyDelete
  13. Hi,

    I'm going to work on turning all my examples into a sample application over the next week or 2. I'll post a comment on this page, or just a blog entry about it.

    Martin

    ReplyDelete
  14. Hi Martin,

    I just came across your post and this feature of column groups is really useful for report that I need to create.

    However it's not working for me.
    I use apex 3.1.

    I created the interactive report groups as per Dimitri's blog and then used your code.

    STEP 1 :I created PL/SQL AP_GET_IR_COL_GROUPS on "On Load(After Header)" . I assume that this is the "page template header" (Or is it different) ?

    STEP 2 :And then I added Javascript Code to Page"Footer Text".

    When I run the page I get following javascript error in IE "Object Expect" at line "$(document).ready(function()".

    No error in Netscape but it also does not show the grouping.

    Kindly help.

    ReplyDelete
  15. Hi Sam,

    I think it could be because you're not using jQuery. Have you included it?


    Martin

    ReplyDelete
  16. Hello Martin,

    This is a great post indeed.

    I'm having a little problem with your code, unfortunatelly. I tried Firebug on it and it seems that, for my case, the following line returns always undefined:

    if (typeof($('.apexir_WORKSHEET_DATA tr:eq(1) th:eq(' + i + ') div').attr('id')) != "undefined")

    I'm using Apex 3.2.0.0.7 and JQuerry 1.2.6 (tried it with your version too - 1.3.1)...

    Its kinda stupid because everyone says its working, but for me it doesnt :(.

    Thank you very much for your help.

    ReplyDelete
  17. Hi DD,

    Can you please put your application, or a portion of it, on apex.oracle.com so I can try to debug the issue there?

    Giffy

    ReplyDelete
  18. Hello Martin,

    Thank you very much for your quick reply.
    Here it is the link:
    http://apex.oracle.com/pls/otn/f?p=37808:1

    On page 1, the report in second column:

    Username: demo
    Password: reportingdd

    If you want to change anything to the application, I can also give you the login to the entire workspace (just created it).

    As you can notice, the report is changed to have lines in between the cells, as its requested in the call: $(vTbl).attr("border","1");

    However, for somereason, the column group headings are not appearing.

    This must be something rly stupid :(.

    Thank you very much for your help,

    Best regards,
    Dan.

    ReplyDelete
  19. Hi Dan,

    I apologize about the delay.

    I took a lot at your example and you may be having issues because of the jQuery version you're running. Could you try using the latest version?

    If that doesn't work, can you please give me access to your workspace? You can add me on linked in (http://ca.linkedin.com/in/martingiffydsouza) if you want to send me the login credentials privately.

    Martin

    ReplyDelete
  20. Martin,

    I finally got a chance to try implementing this in new application.

    I'm sorry to seem whiny, but I would have appreciated it if the post clearly stated that JQuery is required. We don't normally use it and I spent a bunch of time trying to debug this before I noticed mention of JQuery in the comments.

    It's still very cool and I'm going to use it.

    Thanks very much

    - Stew

    ReplyDelete
  21. Hi Stew,

    Thanks for the feedback. I've updated the post to include a note that jQuery is required for this solution.

    Martin

    ReplyDelete
  22. I am having Oracle Apps 11.5.10 installed on Oracle DB 10g. I have a windows XP machine conencted to it and doing APEX 3.1 developement thru it. I am not able to generate 'Interactive Reports' when I click in graphs it gives me 'Any charts' error. DO I need to do some settings on the Linux Box??

    ReplyDelete
  23. Hi Charu,

    Please direct your question to the APEX forums: http://forums.oracle.com/forums/forum.jspa?forumID=137

    Martin

    ReplyDelete
  24. I did in Apex forum but no answer this is the exact error I get, Please help ASAP

    Flash Security Error:
    AnyChart cannot be launched due to Flash Security Settings violation
    Please refer to Security Error Article in AnyChart Documentation to fix this issue

    ReplyDelete
  25. Hi Martin,

    Can you help.. This is become quite urgent now. I did not get any response from Oracle Forums

    ReplyDelete
  26. Hi Charu,

    Can you please email me your application or post it on apex.oracle.com and send me a login credential? martin at clarifit.com

    Thank you,

    Martin

    ReplyDelete
  27. Here is my OTN Post

    http://forums.oracle.com/forums/thread.jspa?threadID%20=1039490&tstart=0

    I have dopped you email seperately

    ReplyDelete
  28. Hi, i am using apex 4, i am getting the Warning: Unknown pseudo-class or pseudo-element 'eq'. In Firefox

    Should i update the jquery also, if so, how can i do that.

    Thanks in advanced

    ReplyDelete
  29. Hi Fernando,

    I haven't tested this code in APEX 4.0 yet. Instead of debugging it I plan to write a plugin for it and post it soon.

    Martin

    ReplyDelete
  30. Really Its very helpful.!! Nice one

    ReplyDelete
  31. Hi all, im very new to Apex so please accept my apology if this is a dumb question.

    I have followed these instructions but my groups are not appearing, my question is how is this actually invoked? do i need to put something like an onload somewhere in my page to call the js? Also i assume I put the js in this page into my HTML header and surround with tags, is this correct?

    Thanks

    ReplyDelete
  32. Hi,

    I've created a plugin for this that will simplify things: http://www.talkapex.com/2010/12/column-groups-in-apex-40-interactive.html

    Martin

    ReplyDelete
  33. Hi Martin,

    Your solution is working good in Apex 4.0, we just put like you post for us.

    Thanks

    Best Regards,
    Jefferson G. Camargo

    ReplyDelete
  34. Hi Martin,

    Nice post. I wanted this group column header to be variable. Is it possible to do that. I tried using &P1_X.

    This did not help. Any suggestions on how to do this.

    Thanks,
    Rama

    ReplyDelete
  35. Hi Rama,

    You'll need to modify the APEX plugin's PL/SQL code. Look for the line containing "column_group":"' and wrap the variable x.column_group in an apex_application.do_substitutions function (see: http://www.talkapex.com/2009/07/apexapplicationdosubstitutions.html)

    Martin

    ReplyDelete
  36. Thanks for the post, it was very helpful...
    but the headings alignment is in left.....
    what to do for getting the center alignment for the group headings.
    Thanks in Advance.

    Shalini

    ReplyDelete
  37. Hi Martin,

    Nice solution. Can we include Groups in our export file as well when we choose Download option from Actions menu?

    Thanks,
    Zahid

    ReplyDelete
    Replies
    1. Out of the box, no. You may be able to do this if you have a print server and build your own reports.

      Delete