Visualizing and Analyzing Data

Oracle APEX's native region components simplify visualizing and analyzing data.

After choosing the region type that fits your requirements, configure the data it should use and adjust its settings in the Property Editor to fine-tune look and behavior. Consider installing the Sample Reports app from the Gallery to study additional useful examples that employ the different kinds of regions described in this section.

Using the Refresh dynamic action, you can refresh any of the regions in response to a user action to show updated data.

• Listing Data with Classic Reports
  The Classic Report region displays data in a table, with optional pagination.
• Personalizable Interactive Reports
  The Interactive Report region is a tabular display that lets users filter, sort, highlight, format, compute, aggregate, group, pivot, chart, download, and email the data you configure it to work with.
• Flexibly Styling and Acting on Data
  The Content Row region lets you flexibly style each row of data.
• Info Tiles Using Cards
  To show results as information tiles, use a Cards region.
• Info at a Glance with Charts & Dashboards
  Use a Chart region to present data visually using one of 18 different styles. You can add multiple charts on a page to create a dashboard. Most chart types can show multiple series of data.
• Showing Data on Maps
  Use a Map region to display data related to specific locations or areas on the earth. One map can show multiple layers when needed. For each layer you configure a table, SQL query, or other data source, and choose a layer type.
• Placing Events on a Calendar
  Use a Calendar region to display scheduled events with a month, week, day, or list view.
• Exploring Hierarchical Data in a Tree
  Use the Tree region to show hierarchical data like the management chain of employees in the EMP table. APEX can manage the hierarchy so you only need to configure the data to display in each tree node.
• Dynamically Formatting Data
  When none of APEX's other region types meet your requirements, the Dynamic Content region lets you programmatically generate all its HTML markup. Your code constructs a CLOB containing any mix of tags and data and returns it for the APEX engine to include in the appropriate place in the page.

The Classic Report region displays data in a table, with optional pagination.

If the number of rows is reliably small, you can show them all at once. When the result set may be larger, configure pagination to efficiently retrieve rows page by page. By adjusting a column's settings in Page Designer, you control its display type, heading, format mask, sort behavior, alignment, and more. For a column of type Plain Text, you can use an HTML Expression to customize how it renders. You can mix HTML tags, conditional formatting directives, and column values, referencing even hidden ones using #SOME_COLUMN_NAME#. As shown below, when the value of a column like Deptno is a foreign key, set its type to Plain Text (based on List of Values) to have APEX automatically render its user-friendly display value instead.

The Standard report template is the default, but the Universal Theme Reference App at oracleapex.com/ut shows other available templates like Value Attribute Pairs, Media List, Search Results, Timeline, and more. When none of the built-in templates meet your needs, you can create custom templates to format the rows and columns of data in a classic report in any way necessary.

For more information, see Managing Classic Reports in Oracle APEX App Builder User’s Guide.

The Interactive Report region is a tabular display that lets users filter, sort, highlight, format, compute, aggregate, group, pivot, chart, download, and email the data you configure it to work with.

For each interactive report, you control the features end users can access. You can also pass dynamic filters when linking to a target page to proactively show the user only the rows they need to see.

The figure below shows an Employees interactive report region where the user has:

• added a computed Earnings column whose formula is Salary plus Commission
• hidden the Salary and Department columns
• defined an aggregate to show the sum of Earnings
• sorted the data by Earnings from highest to lowest
• filtered the results to show only departments 20 and 30 with salaries between 800 and 2000
• highlighted rows with Earnings greater than 1500, and
• formatted the results to list employees in each department in a separate section.

When the type of an interactive report column is Plain Text, you can use an HTML Expression to customize how it renders. You can mix HTML tags, conditional formatting directives, and column values, referencing even hidden ones using #SOME_COLUMN_NAME#.

By default, your interactive report region has a primary default report configuration. Running your page from App Builder, you can set up all aspects of the primary report and save it as the primary default report for all users. This includes which columns should display by default, the order of the columns, the default sort order, and all other features available to configure.

You can also define and save one or more alternative default reports with a name that users can select from a list in the toolbar when multiple options exist. Any alternative default reports show in Page Designer under the Saved Reports heading in the component tree.

End users can also configure and save private reports for any number of reports they run frequently. The private reports show up in the same list as the public default reports, but each user only sees the ones they have created. They can also email any report on demand or subscribe to periodically receive an email containing a selected report.

For more information, see Managing Interactive Reports and Linking to Interactive Reports in Oracle APEX App Builder User’s Guide.

The Content Row region lets you flexibly style each row of data.

For each of its different display slots, you can mix HTML tags, conditional formatting directives, and column values, referencing even hidden ones using &SOME_COLUMN_NAME.. The region in the figure below uses &ENAME. in the Title slot, &ICON. in the Avatar Icon slot, and in the Description slot:

&JOB. in &DNAME. (&LOC.) since &HIREDATE.

Its Overline slot contains the conditionally formatted expression:

Compensation: &SAL. {if COMM/} + &COMM. commission = &TOTAL_COMP.{endif/}

You can define actions like the Edit and Promote ones below to let end users take actions on any row. Due to the conditional formatting directive {if COMM/} above, notice that employees FORD and SCOTT who have no commission get formatted differently. Format masks for the SAL, COMM, TOTAL_COMP, and HIREDATE columns in the region's query are configured as needed in the column settings. In the figure, five employees appear per page with custom formatting and row-specific actions.

When needed, you can enable single- or multi-row selection behavior and configure a hidden page item to contain the primary keys of the selected rows. The page is also using an Order By Item to let end users choose between a number of useful sorting orders. The figure below shows the same region after enabling these features with MARTIN, WARD, and SCOTT selected. For more information, see Managing Sorting with Order By Item in Oracle APEX App Builder User’s Guide.

To show results as information tiles, use a Cards region.

&JOB. in &DNAME. (&LOC.)<br>
since &HIREDATE.

Compensation: &SAL. {if COMM/} + &COMM. commission = &TOTAL_COMP.{endif/}

You can define actions to trigger when the user clicks on the full card, on title or subtitle, or to appear as buttons on each card to let end users take actions on any row. Due to the conditional formatting directive {if COMM/} above, notice that employees who have no commission get formatted differently. Format masks for the SAL, COMM, TOTAL_COMP, and HIREDATE columns in the region's query are configured as needed in the SQL statement you write to determine the cards region's data source.

Your cards can also display images from BLOB columns or URLs, positioned and sized in various ways to complement the other content on the card, if any. When cards include media, actions can target a click on the image as well. If instead you configure them as buttons, they appear at the bottom of the card. The figure below shows results as a grid of information tiles, with a headshot image on each employee's card. Custom formatted data appears in multiple slots, and a Promote button shows on each tile. For more information, see Managing Cards in Oracle APEX App Builder User’s Guide.

Use a Chart region to present data visually using one of 18 different styles. You can add multiple charts on a page to create a dashboard. Most chart types can show multiple series of data.

• Choosing a Chart Type
  When creating a chart region, choose the chart type from the list shown in the Create Page wizard.
• Defining Chart Series
  Configure a table name, SQL query, or other data source to define a chart series. While Gantt, Status Meter Gauge, and Stock charts have a single series, the other types all support showing multiple series on the same chart.
• Including Related Data on a Chart
  Include related data on charts by using SQL.

Use a Map region to display data related to specific locations or areas on the earth. One map can show multiple layers when needed. For each layer you configure a table, SQL query, or other data source, and choose a layer type.

• Choosing a Map Layer Type
  A map can show multiple layers. For each one, configure a table, SQL query, or other data source, and choose a layer type.
• Adding a Layer of Points
  Consider a map layer that shows each employee's home address as a point by joining EMP with a separate EMP_ADDRESSES table. You can configure a single data source at the map region level, then reference it in the layers. Or each layer can define its own data source.
• Conveying Quantities with a Heat Map
  A Heat Map layer associates an additional quantity to a point to convey how that value differs at different points on the map.
• Simplifying Points and Distance
  The SDO_GEOMETRY type lets your applications store, search on, and manipulate spatial data.
• Distance from a Point of Reference
  Using a database view, you can simplify pages that present the distance between points using helper functions.

Use a Calendar region to display scheduled events with a month, week, day, or list view.

If your data source has only an event title and a start date, then the events show on the day they occur. If you enable the Show Time switch, they display as a one-hour event. If you also configure an end date, then the calendar shows the event duration as well.

• Configuring a Basic Calendar
  After adding a calendar, configure its key settings to identifying the events it will display. After choosing a data source, set properties like the event Title, Start Date, and End Date.
• Adding Create and Edit Links
  Use the Property Editor to configure create and edit links that navigate to appropriate pages to enter or modify an event.
• Changing Events with Drag and Drop
  If you enable drag and drop support, then the user can drag the event to a new starting day or time and they can drag its top edge or bottom edge to change the duration.
• Joining In Additional Info Using a View
  Customize additional information in a calendar using a view.
• Enhancing Event Display
  To further customize calendar event display, you can use a custom formatting notation, paired with an event content formatter function.

Use the Tree region to show hierarchical data like the management chain of employees in the EMP table. APEX can manage the hierarchy so you only need to configure the data to display in each tree node.

• Configuring a Basic Tree Display
  After selecting a table like EMP, configure at least a Node Id column, a Node Label column, and a Parent Key column. The latter value references another node id as its parent to form the hierarchy.
• Tracking and Remembering Selection
  Tracking and remembering the tree selection requires configuring a hidden page item with its Value Protected property set to false. You'll use this to store the node value of the selected node. If this hidden page item's value is set during page rendering the tree uses it to restore the selected node.
• Adding Icons to the Tree
  You can use more meaningful node icons to communicate additional information to end users. To control the icon of every node in the tree, provide an additional column whose value indicates the Font APEX icon name to use.
• Parent/Child Data in a Tree
  You can show parent/child data in a tree by using a UNION ALL query and cleverly adjusting the foreign key value.

When none of APEX's other region types meet your requirements, the Dynamic Content region lets you programmatically generate all its HTML markup. Your code constructs a CLOB containing any mix of tags and data and returns it for the APEX engine to include in the appropriate place in the page.

• Generating HTML Tags to Present Data
  When requirements demand total control over HTML markup, use a Dynamic Content region.
• Using CSS for Styling
  Your Dynamic Content region's generated HTML markup can reference Cascading Style Sheets (CSS) classes to fine-tune its styling.
• Referencing Page Item Values
  As with other region types, you can use a dynamic action to interactively refresh the data a Dynamic Content region displays.

When creating a chart region, choose the chart type from the list shown in the Create Page wizard.

Supported chart types include Area, Bar, Box Plot, Bubble, Combination, Status Meter Gauge, Donut, Funnel, Gantt, Line, Line with Area, Pie, Polar, Pyramid, Radar, Range, Scatter, and Stock. To build a dashboard, just add multiple charts to a page and configure their grid layout like any other region. The figure shows the page of the Create Page wizard where you choose the type of chart to create, featuring a visual representation of every type.

Configure a table name, SQL query, or other data source to define a chart series. While Gantt, Status Meter Gauge, and Stock charts have a single series, the other types all support showing multiple series on the same chart.

The figure below shows a bar chart showing the sum of employee salaries and commissions by department number (e.g. 10, 20, 30). By configuring the chart to stack its series, the bar representing department 30 commissions is placed atop the one for salaries instead of appearing next to it.

The figure below shows Page Designer with the Salaries series selected in the component tree. Both series use the EMP table as their data source. Notice in the Column Mapping section of the Property Editor that you configure label, value, and aggregations by choosing appropriate column names.

Include related data on charts by using SQL.

Showing the name of the department instead of the department number requires using a SQL Query to join the EMP and DEPT tables. You can switch the Salaries series to use a SQL Query like the following that joins the two tables on their related key value, and computes the SUM of the SAL values, grouping by department number:

SELECT E.DEPTNO,
       D.DNAME,
       SUM(E.SAL) AS TOTAL_SALARIES
  FROM EMP E
  JOIN DEPT D
    ON D.DEPTNO = E.DEPTNO
GROUP BY E.DEPTNO, D.DNAME

The Commissions series requires a similar query, but since COMM can be null, you need the NVL()function to provide a default value of zero (0) when the commission is null. Notice the SQL language is case-insensitive, so you can use a query in all lowercase if you find that easier to read:

select e.deptno,
       d.dname,
       sum(nvl(e.comm,0)) as total_commissions
  from emp e
  join dept d
    on d.deptno = e.deptno
group by e.deptno, d.dname

After adjusting the Salaries and Commissions series' Column Mapping sections to pick DNAME for the label and the TOTAL_SALARIES and TOTAL_COMMISSIONS for the respective value column, you can configure the series to order by the DEPTNO column. These changes produce the chart shown below with the department name now showing along the x-axis.

For more information, see Creating Charts in Oracle APEX App Builder User’s Guide. Consider installing the Sample Charts app from the Gallery to study additional useful chart examples.

A map can show multiple layers. For each one, configure a table, SQL query, or other data source, and choose a layer type.

A layer can display points using a map marker, lines to indicate paths like navigation directions, or polygons to represent any geographic area. Heat Map and Extruded Polygon layers visualize an additional value associated with each point or shape respectively. The figure below shows the five available layer types as visual thumbnails in the Create Page wizard.

Your spatial data can be a pair of latitude and longitude numbers for Points and Heat Map layers, or all layer types can use SDO_GEOMETRY or GeoJSON. You decide which map tools end users see, including ones for zooming, measuring distance, and identifying a search radius. If one of the built-in map backgrounds doesn't meet your needs, you can define a custom one. When your data includes many points that might appear in close proximity, consider enabling point clustering for a cleaner look at lower zoom levels. Using dynamic actions, you can easily react to map changes and object selection.

Consider a map layer that shows each employee's home address as a point by joining EMP with a separate EMP_ADDRESSES table. You can configure a single data source at the map region level, then reference it in the layers. Or each layer can define its own data source.

To simplify maintenance, consider creating a database view to encapsulate the region's SQL query. Then use it as the region's data source and pick column names to use in the layer. For example, consider the following EMP_WITH_ADDRESSES view that joins EMP and EMP_ADDRESSES and includes a computed TOTAL_COMPENSATION column:

create or replace view emp_with_addresses as
select e.empno,
       e.ename,
       a.address,
       a.latitude,
       a.longitude,
       e.sal + nvl(e.comm,0) as total_compensation
  from emp e
  join emp_addresses a
    on a.empno = e.empno;

Then the map uses this view, configures its points using the LATITUDE and LONGITUDE columns, and defines the tooltip using the HTML Expression:

&ENAME. - &ADDRESS.

The figure below shows the resulting map, with its single point layer showing employee addresses around San Francisco, California and Marin County. Notice the user is hovering the mouse over BLAKE's point marker, and the tooltip displays "BLAKE - 1246 Palou Ave, San Francisco, CA 94124".

A Heat Map layer associates an additional quantity to a point to convey how that value differs at different points on the map.

After adding an additional Salaries heat map layer, using TOTAL_COMPENSATION as the value column, the page looks like the figure below when both layers are displayed. Each employee's address marker now shows a circle sized proportionally to their total pay relative to their peers.

The SDO_GEOMETRY type lets your applications store, search on, and manipulate spatial data.

A point is the simplest information an SDO_GEOMETRY value can hold. It can represent addresses and GPS locations. However, this data type also can handle:

• lines – for driving or walking routes or property boundaries,
• polygons – for city limits, sales territories, store footprints, lakes, or forests
• circles – for service areas, or delivery or impact zones.

Focusing on the points you need for the current task, in Oracle AI Database 26ai, you create an SDO_GEOMETRY for a point using the simple expression:

SDO_GEOMETRY(longitude,latitude)

However, in earlier database versions, the following helper functions can simplify your work. The MAKE_POINT function returns an SDO_GEOMETRY value for longitude and latitude:

function make_point(
    p_longitude in number,
    p_latitude  in number)
return sdo_geometry
deterministic
is
    geom_type_2d_point   constant number := 2001;
    spatial_ref_type_gps constant number := 4326;
begin
    return case
              when     p_longitude is not null
                   and p_latitude  is not null
              then
                 sdo_geometry(
                    geom_type_2d_point,
                    spatial_ref_type_gps,
                    sdo_point_type(p_longitude, p_latitude,
                    null), null, null)
            end;
end;

The DISTANCE_IN_KM function returns the distance between two SDO_GEOMETRY points:

function distance_in_km(
    p_origin      in sdo_geometry,
    p_destination in sdo_geometry
)
return number
deterministic
as
begin
    return sdo_geom.sdo_distance(
            p_origin,
            p_destination,
            tol=>1,
            unit=>'unit=KM');
end distance_in_km;

Using a database view, you can simplify pages that present the distance between points using helper functions.

create or replace view emp_with_addresses as
select e.empno,
       e.ename,
       a.address,
       a.latitude,
       a.longitude,
       e.sal + nvl(e.comm,0) as total_compensation,
       round(distance_in_km(
                make_point(a.longitude,a.latitude),
                make_point(-122.402768,37.795270)),1)
       as distance_from_office_km
  from emp e
  join emp_addresses a
    on a.empno = e.empno

Then, you could enhance the example map from to include a third layer to represent the office building's location using a query like this:

select   37.795270 as latitude,
       -122.402768 as longitude
  from dual

Updating the Employees layer tooltip HTML expression to include the distance, you can include that information when end users hover over an employee's house:

&ENAME. - &ADDRESS. (&DISTANCE_FROM_OFFICE_KM. km)

These changes produce the map shown below. Notice the user's mouse hovering over SCOTT's map marker. The tooltip now reveals she lives 10.2km from the office. For more information, see Creating Maps in Oracle APEX App Builder User’s Guide, and consider installing the Sample Maps app from the Gallery to study additional useful map examples.

After adding a calendar, configure its key settings to identifying the events it will display. After choosing a data source, set properties like the event Title, Start Date, and End Date.

Imagine an EMP_MEETINGS table that stores scheduled events between one employee and another. The diagram in the figure below shows how the EMP table and EMP_MEETINGS relate. This new table contains a CALENDAR_FOR_EMPNO column for the inviting employee and a MEET_WITH_EMPNO column for the invitee.

The figure below shows the week view of a calendar region based on the EMP_MEETINGS table after configuring the PURPOSE column as the event title, STARTS_AT as the start date, and ENDS_AT as end date. You can configure the calendar to not show weekends, to allow month, week, and day views, and to show the navigation buttons to let users change the current month, week, or day.

   CALENDAR_FOR_EMPNO = :P19_CALENDAR_FOR
OR MEET_WITH_EMPNO    = :P19_CALENDAR_FOR

Use the Property Editor to configure create and edit links that navigate to appropriate pages to enter or modify an event.

To define the create link, use the Link Builder dialog to define the target page. Once configured, the user can click on the calendar or click-and-drag to create a new event. The figure shows configuring the create link to target page 20 and pass the value of P19_CALENDAR_FOR as the value of the target page's P20_CALENDAR_FOR_EMPNO page item.

Notice you can easily pass the APEX$NEW_START_DATE and APEX$NEW_END_DATE values to your event creation page using the &NAME. syntax. In a similar way, after configuring a target link for event editing, then a user can click on an existing event in the calendar to opens a page to edit the details.

If you enable drag and drop support, then the user can drag the event to a new starting day or time and they can drag its top edge or bottom edge to change the duration.

You only need to write the logic to update your event with the new start and end dates like this. APEX formats the start and end date values as a canonical 12-digit number representing a date like April 19th, 2025 at 9:30am as 202504190930. You reference APEX$NEW_START_DATE and APEX$NEW_END_DATE as bind variables along with APEX$PK_VALUE for the primary key of the meeting that the user modified.

update emp_meetings
   set starts_at = to_date(:APEX$NEW_START_DATE, 'YYYYMMDDHH24MISS'),
         ends_at = to_date(:APEX$NEW_END_DATE,   'YYYYMMDDHH24MISS')
 where id = :APEX$PK_VALUE;

Customize additional information in a calendar using a view.

To show additional information in the calendar for each event like the name of the employee invited to the meeting, you could create an EMP_MEETINGS_V view to join the EMP_MEETINGS table with EMP to retrieve the inviting employee name and the invited employee name like this:

create or replace view emp_meetings_v as
select m.id,
       m.purpose,
       m.starts_at,
       m.ends_at,
       m.calendar_for_empno,
       cal_e.ename  as calendar_for_ename,
       m.meet_with_empno,
       meet_e.ename as meet_with_ename
  from emp_meetings m
  join emp cal_e  /* inviting emp */
    on cal_e.empno = m.calendar_for_empno
  join emp meet_e /* invited emp  */
    on meet_e.empno = m.meet_with_empno

You could then change the region to be based on the SQL Query below that selects from this new view. It uses a CASE expression as part of a FORMATTED_TITLE column to show either the MEET_WITH_ENAME or the CALENDAR_FOR_ENAME value, depending on the value of the P19_CALENDAR_FOR page item.

select id,
       purpose||' with '||
          case to_number(:P19_CALENDAR_FOR)
             when calendar_for_empno then meet_with_ename
             else calendar_for_ename
          end as formatted_title,
       starts_at,
       ends_at
  from emp_meetings_v
where calendar_for_empno = :P19_CALENDAR_FOR
   or meet_with_empno    = :P19_CALENDAR_FOR

After changing the event title to use the new FORMATTED_TITLE column, the calendar looks like the figure below showing the name of the person KING is meeting with.

To further customize calendar event display, you can use a custom formatting notation, paired with an event content formatter function.

The SQL below updates the existing query to use the FORMAT function in the APEX_STRING package to inject two values into a string template: [[%s]]|with %s. You supply the PURPOSE column value for the first %s placeholder. To conditionally return either the CALENDAR_FOR_ENAME or the MEET_WITH_ENAME depending on the value of the P19_CALENDAR_FOR select list you use a CASE statement.

select id,
       apex_string.format('[[%s]]|with %s',
          purpose,
          case to_number(:P19_CALENDAR_FOR)
             when calendar_for_empno then meet_with_ename
             else calendar_for_ename
          end) as formatted_title,
       starts_at,
       ends_at
  from emp_meetings_v
where calendar_for_empno = :P19_CALENDAR_FOR
   or meet_with_empno    = :P19_CALENDAR_FOR

The string template value ends up looking like the following for one example EMP_MEETINGS row:

[[Hiring Review]]|with ADAMS

You further customize the calendar event behavior and display by using a JavaScript Initialization Function like the one below. This function configures calendar settings on the pOptions object it is passed as an argument, then returns the modified pOptions object as its result. The function can use any of the features of the open-source FullCalendar library to tailor how the calendar looks and behaves.

The custom event rendering function below replaces the [[, ]], and | with appropriate HTML tags to bold the title and add a line break before the additional detail text. You can't directly use HTML tags in the original string because for security reasons the APEX engine would escape them and it wouldn't have the desired effect. It also sets the calendar locale to en-gb for a UK date format. Notice it configures an eventContent function that returns custom html formatting for the event display. It references the event title using arg.event.title and uses the JavaScript replace() function to replace | by <br>, [[ by <b>, and ]] by </b>. It also wraps the event title content with in an HTML <center> tag.

function (pOptions) {
    pOptions.locale       = "en-gb"; // UK date format
    pOptions.eventContent = function(arg) {
      return {
        html: '<center>' +
              arg.event.title.replace(/\|/g, '<br>')
                             .replace(/\[\[/g,'<b>')
                             .replace(/\]\]/g,'</b>') +
              '</center>'
      }
    };
    return pOptions;
} 

This combination of using the EMP_MEETINGS_V view to join in employee names and custom event title formatting produces the updated calendar you see below. Each event's text is centered, and shows the purpose of each meeting on its own line in a bold font, and the person the meeting is with on a second line. You can customize the calendar further using CSS styling. Consider installing the Sample Calendar app from the gallery to explore additional examples of what the calendar region can do. For more information, see Creating Calendars in Oracle APEX App Builder User’s Guide.

After selecting a table like EMP, configure at least a Node Id column, a Node Label column, and a Parent Key column. The latter value references another node id as its parent to form the hierarchy.

As shown below, you can use the ENAME column for the node label, EMPNO as the node ID, and MGR as the parent key. Notice the Hierarchy property is set to Computed with SQL. This choice means, "Let the APEX engine compute the hierarchy with SQL that it automatically generates at runtime." The Order Siblings By property sets ENAME as the sorting order for nodes in the tree at the same level, while the Start Tree With property setting of Value is NULL causes the top level of the tree to be nodes where the Parent Key Column MGR is null.

These settings produce the tree shown below. KING has a null value for her MGR column, so she displays as the root node of the tree. APEX computed the hierarchical query at runtime, including the configured colums, to retrieve all the tree's data in a single query. In the figure, the CLARK and JONES levels are expanded to reveal the direct reports to multiple levels.

Tracking and remembering the tree selection requires configuring a hidden page item with its Value Protected property set to false. You'll use this to store the node value of the selected node. If this hidden page item's value is set during page rendering the tree uses it to restore the selected node.

Start by defining an item like P17_SELECTED_EMPNO and confirming that its Session State > Storage property is set to Per Session (Persistent). Then you can configure the tree region's Node Value to use the EMPNO column and set the Selected Node Page Item to P17_SELECTED_EMPNO. This takes care of restoring the selected node when the hidden page item is populated.

Completing the picture requires two additional steps. Begin by setting the Static ID property of the tree region, for example, to emptree. Next, define a dynamic action on the tree region to react to the Selection Changed [Tree] event. You can title it When Node Selected, and configure its first dynamic action step to be of type Set Value with Affected Elements of Item P17_SELECTED_EMPNO. Set the Fire on Initialization switch to false and configure the Set Type to be JavaScript Expression with the line below. It returns the node value of the first selected node in the region with Static ID emptree.

apex.region("emptree").call("getSelectedNodes")[0]?.id

If remembering the selected employee number when the user submits the page is adequate, then this is the only additional step required. If instead you want the user's tree selection to immediately be stored in the session, configure an additional dynamic action step of type Execute Server-side Code with its Page Items to Submit property set to P17_SELECTED_EMPNO. The PL/SQL code in this action step can simply be:

null; -- Push Items to Submit into Session State

With these settings in place as shown in the figure below, the tree tracks and remembers the user's selection throughout their session.

You can use more meaningful node icons to communicate additional information to end users. To control the icon of every node in the tree, provide an additional column whose value indicates the Font APEX icon name to use.

You can review the available icon names in your browser in Icons page of the Universal Theme Reference App at oracleapex.com/ut.

For example, if you define the following EMP_WITH_ICONS_V view, it uses a CASE statement to return a different icon name based on the JOB column value of each employee:

create or replace view emp_with_icons_v as
select empno,
       ename,
       job,
       deptno,
       mgr,
       hiredate,
       sal,
       comm,
       'fa '||case job
            when 'PRESIDENT' then 'fa-badgerine'
            when 'ANALYST'   then 'fa-line-chart'
            when 'CLERK'     then 'fa-user-headset'
            when 'SALESMAN'  then 'fa-badge-dollar'
            when 'MANAGER'   then 'fa-users'
       end as icon
from emp

After changing the tree region to be based on EMP_WITH_ICONS_V and setting its Icon CSS Class Column property to ICON, the tree changes to look like the figure below. Each node in the tree renders with a job-appropriate icon.

You can show parent/child data in a tree by using a UNION ALL query and cleverly adjusting the foreign key value.

For example, consider the task of creating the tree shown below. Employees appear in alphabetical order indented under a tree node representing the department they work in. The task is simple, but requires thinking like the APEX tree region to make it happen.

The tree region needs three basic elements: a node label, a node id, and a parent id. When querying hierarchical data from a single table like EMP, you know that the node id EMPNO and the parent id MGR are both employee IDs. However, when creating a hierarchy combining department and employee numbers, it's important to ensure that the node id values don't overlap. You can achieve this by performing a math operation on the department numbers to be sure they cannot clash with the employee numbers. For example, you could multiply the DEPTNO column value by -1 so that the department numbers are negative values, while the employee numbers remain positive values.

The SQL query below uses this technique while combining rows from the EMP_WITH_ICONS_V view and the DEPT table using the UNION ALL set operator. Notice the department rows alias the expression DEPTNO * -1 as the value of the NODE_ID column, and use a NULL value for the PARENT_KEY. Then, in the employee query the same expression is used as the value of the PARENT_KEY.

create or replace view dept_emp_tree_v as
select -1 * deptno        as node_id,
       dname              as node_label,
       null               as parent_key,
       'fa fa-building-o' as icon
 from dept
union all
select empno              as node_id,
       ename              as node_label,
       -1 * deptno        as parent_key,
       icon
 from emp_with_icons_v

Then, you simply need to configure the tree region to be based on this SQL query, configuring the appropriately-named columns as Node ID, Node Label, and Parent Key. Using the Hierarchy setting of Computed in SQL and the Start Tree With property set to Value is NULL, you get a tree whose first level are the department nodes, and whose first level beneath those are the employees who work in the respective department. By setting Order Siblings By to the NODE_LABEL column, you ensure both the departments and employees nodes get sorted alphabetically.

When requirements demand total control over HTML markup, use a Dynamic Content region.

Harkening back to a time when enterprise applications used character mode displays, consider a simple example of an "ASCII art" chart of employee salaries. Of course using an APEX Chart region would look more polished, but this is a fun way to reminisce while learning the basics of the Dynamic Content region. The goal is producing an old-school chart that looks like a Dec VT220 terminal of yore like in the figure below.

After setting the region type to Dynamic Content, you write a PL/SQL block to generate the HTML markup to include in the page. The example below is a function body that returns a CLOB containing the data and HTML tags the chart requires. First you select the maximum salary used for scaling the salary bars, then loop over the employee rows in descending order by salary. For each employee, you calculate the number of block characters for a 40-character-wide result. The block count is the rounded result of dividing the current employee's salary by the maximum salary to get a percentage, multiplied by 40. Using the rpad() function, you produce a string of l_blocks bar characters. Then you include this "bar" in the contents of an HTML <pre> tag for preformatted text, along with the current employee's name and a vertical bar symbol. The code relies on the p() local helper function to incrementally "print" each line of HTML tags and data to the l_content variable. The PL/SQL block returns this variable as its result at the end.

declare
    l_max_sal number;
    l_bar     varchar2(1000);
    l_blocks  number;
    l_content clob;

    procedure p(p_text varchar2)
    is
    begin
        l_content := l_content || p_text;
    end p;
begin
    select max(sal)
      into l_max_sal
      from emp;

    for j in (select ename, sal from emp order by sal desc) loop
      l_blocks := round(j.sal / l_max_sal * 40);
      l_bar := rpad('▒', l_blocks, '▒');
      p('<pre>' || rpad(j.ename, 10) || ' | ' || l_bar
                || ' ' || j.sal || '</pre>');
    end loop;
    return l_content;
end;

Your Dynamic Content region's generated HTML markup can reference Cascading Style Sheets (CSS) classes to fine-tune its styling.

Since you want the result to look like a Dec VT220 terminal, you can define a CSS class named vt220 in your page's CSS > Inline section in the Property Editor. Its name begins with a dot and contains the appropriate style property name/value pairs to give a green, monospace font on a black background with a glowing box shadow. To override the Universal Theme styling of a <pre> tag in the contents of an element with the vt220 class assigned to it, you include a second CSS rule for the .vt220 pre selector to get a tighter line spacing as well.

To assign the vt220 CSS class to the dynamic content region, simply mention it in the Appearance > CSS Classes property of the region without the dot in front of the name. This combination produces the desired result.

.vt220 {
  background-color: black;
  color: #00FF00;
  font-family: 'Courier New', monospace;
  font-size: 14px;
  padding: 1.5em;
  border-radius: 16px;
  border: 3px solid #333;
  box-shadow: 0 0 20px #00ff00aa;
  width: fit-content;
  margin: 2em auto;
}
.vt220 pre {
  line-height: 1.0;
}

As with other region types, you can use a dynamic action to interactively refresh the data a Dynamic Content region displays.

Consider the page shown below having a P22_DEPARTMENT select list using 3 of the 12 columns in the layout grid and an old-school ASCII art chart dynamic content region in the same row. Configure a dynamic action on the select list to react to the Change event with an action step to Refresh the dynamic content region. Then the chart refreshes to show the latest data any time the end user changes the selected department.

To complete the implementation, adjust the dynamic content region's PL/SQL block to reference the :P22_DEPARTMENT page item value in the select max(sal) query as well as in the query in the for loop as shown below:

    -- Adjusted two queries to reference page item value as bind variable
    select max(sal)
      into l_max_sal
      from emp
     where deptno = :P22_DEPARTMENT;

    for j in (select ename, sal from emp
               where deptno = :P22_DEPARTMENT
               order by sal desc)
    loop
      l_blocks := round(j.sal / l_max_sal * 40);
      l_bar := rpad('▒', l_blocks, '▒');
      p('<pre>' || rpad(j.ename, 10) || ' | ' || l_bar
                || ' ' || j.sal || '</pre>');
    end loop;

With these changes in place, the last step is mentioning P22_DEPARTMENT in the dynamic content region's Page Items to Submit property so the APEX engine gets the latest value from the browser automatically whenever the region refreshes.