📋 Smart Hierarchy Reports - Show Child Records Inside Parent Rows in Oracle APEX

🚩Introduction 

• In many Oracle APEX applications, data often has a parent-child relationship, such as departments and their employees, or orders and their order items. Showing this related data clearly, without making the page feel crowded, is important for a good user experience.

• A useful approach is to show the child records right inside the parent row itself, when the user clicks a button. Instead of opening a separate region or navigating to another page, the child data simply expands within the same row. This keeps the report compact, easy to read, and gives users full control over what they want to see.

📑 Why This Approach Is Needed

In Oracle APEX applications, users often need to view related child records without losing context of the parent record they are looking at. If the child data is shown in a separate region or page, users have to keep switching back and forth, which can be confusing.

Showing child data within the parent row becomes useful when:

• Users want to see related details without leaving the current report

• The report should stay clean and compact until the user needs more information

• Parent and child data need to stay visually connected

• Switching between separate regions or pages should be avoided

• A simple click should be enough to view or hide the related data

This approach helps users stay focused, reduces page crowded, and makes it easier to understand the relationship between parent and child records in Oracle APEX.

👉 Use Case: Showing Child Region Within the Parent Row in Oracle APEX

In Oracle APEX, users often need to view child records, such as employees under a department or items under an order, without moving away from the parent report. Adding a button to each parent row allows users to expand and view the related child data directly within that row.

In this blog, we will see how to implement this hierarchy report in Oracle APEX, where clicking a button on the parent row shows the child region inside the same row. This helps users view related data quickly, keeps the report simple, and avoids the need for a separate child region.

Step 1: 

In the page designer, create a region and change the type to Interactive Report and set the source type as SQL query and copy the SQL Query below.

--- DEPARTMENT ---
SELECT DEPTNO,
       DNAME,
       LOC,
       '<span class="show_child" data-emp="' || DEPTNO || '">➕</span>' AS details
FROM DEPT;

 Step 2: 

Create a page item of type Hidden and we are going to hold the primary key of the parent region in this page item.

Step 3: 

Create another region of type Interactive Report and set the source type as SQL query and copy the SQL Query below. We will be passing this page item in the sql query to fetch the respective child records.

--- EMPLOYEE REPORT ---
SELECT EMPNO,
       ENAME,
       JOB,
       SAL,
       DEPTNO
FROM EMP
WHERE DEPTNO = :P98_DEPT_NO;  --Hidden Page item 

Step 4: 

Under the properties, navigate to the Appearance section and select the Blank with Attributes option from the Template property and enter the unique id in the HTML DOM ID.

Step 5: 

Click on the page and navigate to the Inline CSS section and copy paste the below CSS code.

--- Inline CSS Code ---

#RG_EMP{
  display: none;
}

.column-action-btn{
  cursor: pointer;
}

Step 6: 

Now, navigate to the Function and Global variable declaration section and copy paste the below JavaScript code.

--- Function and Global Variable Declaration ---

function showDetail(el, depno) {
  $s('P98_DEPT_NO', depno);  -- Page Item

  let btn = $(el);
  let tr  = btn.closest("tr");

  if (tr.next().hasClass("detail_region")) {
    tr.next().find(".slide-box").stop(true,true).slideUp(500, "swing", function () {
      tr.next().remove();
    });

    return;
  }

  // Close & Remove other open rows
  $(".detail_region").remove();  //Child Row ID

  console.log(apex.item("P98_DEPT_NO").getValue());

  apex.region("RG_EMP").refresh();  //Child Static ID

  setTimeout(() => {

    // Get content
    let content = $("#RG_EMP").html();

    console.log(content);

    // Create row (content hidden initially)
    let newRow = `
    <tr class="detail_region">
      <td colspan="99">
        <div class="slide-box" style="display:none;">
          \${content}
        </div>
      </td>
    </tr>
    `;

    tr.after(newRow);

    setTimeout(function() {
      tr.next().find('.slide-box').stop(true,true).slideDown(500);
    }, 300);

  }, 1000);
}

Step 6: 

Now save and run the page, you will see the report rendered in the page.

🔥 Conclusion

Thus, in Oracle APEX, displaying the child region within the parent row on a button click gives users a simple and effective way to explore related data. This keeps the report clean, avoids unnecessary navigation, and makes it easier to understand parent-child relationships while keeping the implementation simple.