How do I Create a Tree Report in Oracle APEX

 

Introduction
Creating a Tree Report in Oracle APEX is a powerful way to display hierarchical data such as organization charts, category breakdowns, or file structures. The Tree Region in APEX allows you to visually represent parent-child relationships using an intuitive and interactive tree view. In this blog post, we’ll walk through how to create a Tree Report, define the necessary SQL query, and configure the region attributes to present your data in a meaningful and user-friendly way.

How to Create a Tree Report in Oracle APEX

1. Prepare Your Data
   Before you begin, ensure your table contains at least:

• A unique identifier (e.g., ID)

• A parent identifier (e.g., PARENT_ID)

• A label (e.g., NAME)

Your data must form a hierarchy, where each row references a parent row.

2. Create a Tree Region

• Navigate to your page in Page Designer.

• Click the + icon to add a new region.

• Choose Tree under the “Reports” section.

3. Configure the Tree Region Source
   Use a hierarchical SQL query with the CONNECT BY clause:

SELECT 
  ID, 
  PARENT_ID, 
  NAME AS LABEL 
FROM 
  TREE_TABLE
START WITH 
  PARENT_ID IS NULL
CONNECT BY 
  PRIOR ID = PARENT_ID

Map these values in the region’s Attributes section:

• Node ID Column → ID

• Parent Node Column → PARENT_ID

• Node Label Column → NAME or a derived label

4. Set Node Icons (Optional)
   If your table contains a column for icons or if you want conditional icons, use the Node Icon Column setting. You can return class names like 'fa fa-folder' or use the APEX icon library.

5. Add Links or Actions (Optional)
   You can define a Link Target so that clicking on a node opens a specific page (such as a detail form). Use column substitutions like:

f?p=&APP_ID.:10:&SESSION.::NO::P10_ID:#ID#

This navigates to page 10 and passes the selected node's ID to P10_ID.

Best Practices

• Limit the depth of the tree if your dataset is large—consider using lazy loading with dynamic actions if needed.

• Make sure your parent-child relationships do not contain cycles, or the tree will fail to render.

• Use descriptive labels and clear icons to improve usability.

• Consider using a REST-enabled SQL source or a view for performance optimization and cleaner design.

Oracle APEX Documentation
For additional configuration options and advanced use cases, refer to:
Oracle APEX Tree Region Documentation

 

A Tree Report in Oracle APEX provides a structured way to display hierarchical data, such as organizational charts, category structures, or folder directories. This tutorial explains how to create, configure, and customize a Tree Report in APEX.

Understanding Tree Reports

A Tree Report is used to display parent-child relationships in a hierarchical format. It consists of:

• Node (Parent Item): The main category or entity in the hierarchy.

• Child Nodes (Sub-items): The subcategories or records linked to the parent.

• Tree Structure: Expanding and collapsing nodes dynamically.

Tree Reports are best used when data follows a hierarchical structure, such as departments and employees, product categories and subcategories, or folders and files.

Conclusion
The Tree Region in Oracle APEX is a versatile and elegant solution for presenting hierarchical data structures. With proper setup of your data and careful configuration of region attributes, you can deliver an intuitive visual experience to your users. Whether you're organizing categories, departments, or nested items, APEX Tree Reports help bring structure and clarity to your applications.