How to create your own customized nested report regions using jQuery

Here is how you can create cool nested reports in Oracle APEX and jQuery, which looks like below:


For this example, I have used DEPT & EMP example where as a nested report it will show all the child records of employee under department record.

To start with, first create a new classic report region with query like "select * from dept".

Add a new derived column of type = Link to that report and set following attributes:
Link: javascript: void(0);
Link Text: <i class="fa fa-users" alt="Employees" title="Employees"></i>
Link Attribute: class="viewEmp" data-deptno="#DEPTNO#"

Add new page item to hold deptno value and call it PX_DEPTNO

Create new classic report region (this will be your nested region) and set query like "select * from emp where deptno = :PX_DEPTNO" and set following attributes:
Static ID: EMPLOYEES
Custom Attributes: style="display: none"
Page Items to Submit: :PX_DEPTNO

In page settings > function and global variable declaration, define new variable gThis as below.
var gThis;

Create new dynamic action as follow:
Event: Click
Selection Type: jQuery Selector
jQuery Selector: a.viewEmp

Add true action 1 as below:
Action: Execute JavaScript Code
Fire on Initialization: No
Code:
$('tr.my_custom_row').hide(500, function(){$(this).remove();});
gThis = $(this.triggeringElement);

Add true action 2 as below:
Action: Set Value
Set Type: JavaScript Expression
JavaScript Expression: $(this.triggeringElement).attr("data-deptno")
Fire on Initialization: No

Add true action 3 as below:
Action: Set Value
Set Type: PL/SQL Expression
PL/SQL Expression: :PX_DEPTNO
Item to Submit: PX_DEPTNO
Fire on Initialization: No

Add true action 4 as below:
Action: Refresh
Selection Type: Region
Region: Employee (Your second region)
Fire on Initialization: No

Create new dynamic action as below:
Event: After Refresh
Selection Type: Region
Region: Employee (Your second region)

Add true action as below:
Action: Execute JavaScript Code
Fire on Initialization: No
JavaScript Code:

var vThis = gThis;
var vTRID = $(vThis).attr("data-deptno");
var vColSpan = $(vThis).closest("tr").find("td").length;
var vClass = $(vThis).closest("td").attr("class");
var vTR = $(vThis).closest("tr");
var vReportHTML = $('#EMPLOYEES').clone();
vReportHTML = $(vReportHTML).removeAttr("id");
vReportHTML = $(vReportHTML).css("display", "block");
var vReportHTMLFinal = $('<div>').append($(vReportHTML)).html();
$(vTR).after('<tr id="EMPLOYEES_'+vTRID+'" class="my_custom_row" style="display: none;"><td class="'+vClass+'" colspan="'+vColSpan+'" style="padding: 15px;">'+vReportHTMLFinal+'</td></tr>');
$('tr.my_custom_row').show(500);

That's it. ENJOY!!!

Click here to see demo in action.

Hope this helps.

Regards,
Jaydip Bosamiya
jbosamiya@gmail.com

Comments

  1. Hi,

    I am trying to get into JQuery but its really hard for someone starting from scratch. This report uses a lot of the features I have been trying to learn.
    So really appreciate if you could explain what each line in the javascript code at the end actually does. That would really help.

    Regards,
    leckj

    ReplyDelete
  2. Hi,
    What is the Item to Submit in Action 2 ?

    ReplyDelete
  3. Nice great job. Explained well.

    This really works great for a single nested report. Is there a way to expand this for more nested reports. It seems that if you just repeat the steps for the 1st nested report in the 2nd nested report the events do not work. Thanks for any insight you might be able to give



    ReplyDelete
    Replies
    1. Thanks Wayne for your feedback. I haven't tried multiple nested report in a same page, but it should be for sure possible - just that we should take a separate "Global Variable" and IDs. Let me where you are getting an error or any specific restriction.

      Delete
  4. Hi, What would be the Affected Elements detail for Set Value Action?

    ReplyDelete
  5. Hi,
    Closing of nested report doesn't work if the nested report is interactive report,it works perfectly fine with classic Report.Please do give solution to close nested report(IR) on click.

    Thank You

    ReplyDelete
    Replies
    1. Thank you for the feedback. Yes it will not work in case of IR. IR has a different dom structure as well all "Action" button things. I will give a try and if successful, I will update you. Thanks.

      Delete
  6. Hi, How do you get "tr.my_custom_row"?

    ReplyDelete
  7. Hi,

    The design is superb I tried to implement it but problem is when filter is applied for the main report, I am using interactive report for the main report as requirement is that user must be able to filter the values, so when filter is applied then we have to reload the page then it opens the nested report else do nothing. Every time I have to reload the page. Please provide suggestions.

    ReplyDelete

Post a Comment

My photo
Jaydip Bosamiya
I am Oracle APEX Consultant, Blogger and Integrator. All-rounder in building small, medium and enterprise applications. Extensive knowledge in various area of web-driven applications in Back-end (PL/SQL, SQL, Java), Front-end (Oracle APEX, HTML, JavaScript, CSS, jQuery, OracleJET, ReactJS), RESTful APIs, Third-party library integrations (Apex Office Print (AOP), Payment Gateways, Syncfusion, HighCharts) and APEX Plugins (HighChart, StarRating)

Popular posts from this blog

Oracle APEX - Interactive Report - Scrollbars on Top

Make your RESTful APIs Secured with OAuth2 - Basic, Simple and Easy Steps