1. Overview
The document will talk about nesting a report inside another report using the technologies like Oracle APEX, JavaScript, HTML. In Oracle APEX, embedding a nested report within a customized classic report provides a powerful means of presenting related data hierarchically. The process begins by creating a classic report page, tailored to meet specific design requirements and powered by a SQL query fetching primary data. Configure a link within the master report’s attributes, utilizing dynamic actions and JavaScript for seamless navigation between the primary and nested reports.
2. Technologies and Tools Used
The following technologies has been used to achieve the same.
- Oracle APEX
- JavaScript
- HTML
3. Use Case
This kind of methodology will be more useful when we are dealing with hierarchical data where we don’t need to rerouting the user to a different page rather we can use a single region and child records can be nested inside that Which will provide better visual of data to the end user.
4. Architecture
Step1:
The first step in this methodology is to create two report regions having two different source table but should have parent child relationship so we can fetch particular parent row’s child records and nest inside corresponding rows.
Here I have two reports namely,
One is, Parent Records with the source code of the below.
SELECT PARENT_ID,
PARENT_NAME
FROM PARENT_TABLE;
Second One is, Child Records with the source code of the below.
SELECT CHILD_ID,
PARENT_ID,
CHILD_NAME
FROM CHILD_TABLE
WHERE PARENT_ID = :P1_PARENT_ID;
From the above second region’s source code, I have used P1_PARENT_ID page item which will hold parent_id value of a row that we want to see the child records.
Step2: In the parent records report I just made the Parent ID column as link and changed the link attribute and link target as below.
Target – javascript:void(0);
Link Text – <span class=”fa fa-eye” aria-hidden=”true”></span>
Link Attributes – class=”viewChild1″ data-parId=’#PARENT_ID#’
As you can see from the image above I set class and one data attribute of parId which will hold the value of Parent ID of a each row.
Step3: Then I created dynamic action based on the JQuery selector of class attribute which we set on the link column. Under this dynamic action created 3 true actions those will take care of the function of setting parent ID of a row into the page item called P1_PARENT_ID which we created earlier.
First action is, Execute JavaScript Code which will extract the value of the parent id of a row based on the click event and set the value to the P1_PARENT_ID page item.
The code is,
$(‘tr.my_custom_row’).hide(500, function() {
$(this).remove();
});
gThis=$(this.triggeringElement)
$(‘#P1_PARENT_ID’).val(gThis.attr(‘data-parId’));
The second action is, Set Value
Finally 3rd one is, Refresh which will refresh the child records region on successful setting of parent id value in P1_PARENT_ID page item.
Step4: Final step is cloning the child records region which will now contain only the children’s of the parent record that we clicked and append the region under the row that we clicked. The action will start right after the refresh of the child records region.
This dynamic action will contain only one true action Execute JavaScript Code. The JS code is,
var vThis = gThis;
var vTRID = $(vThis).attr(“data-parId”);
var vColSpan = $(vThis).closest(“tr”).find(“td”).length;
var vClass = $(vThis).closest(“td”).attr(“class”);
var vTR = $(vThis).closest(“tr”);
var vReportHTML = $(“#CHILD-TBL”).clone();
vReportHTML.removeAttr(“id”);
vReportHTML.css(“display”, “block”);
var vReportHTMLFinal = $(“<div>”).append(vReportHTML).html();
$(vTR).after(‘<tr id=”QAM_’ + vTRID + ‘” class=”my_custom_row” style=”display: none;”><td class=”‘ + vClass +
‘” colspan=”‘ + vColSpan + ‘” style=”padding: 15px;”>’ + vReportHTMLFinal + ‘</td></tr>’);
$(‘tr.my_custom_row’).show(200);
That’s all we have; we’ve come to the end of this blog. The final output will be like the below.
5. Conclusion
The integration of a nested report within a customized classic report in Oracle APEX offers a powerful solution for presenting data hierarchically. The use of cloning to create a child table enhances the flexibility and interactivity of the user interface. This approach not only allows for a concise overview of essential information but also facilitates seamless navigation to more detailed data when needed. The customization options, including tailored titles, layout adjustments, and dynamic actions, contribute to a user-friendly experience.
