Oracle APEX Part 9 - Interactive reports with Charts in Oracle APEX

Adding a Chart to an Interactive Report (IR) is one of the best ways to make your data "pop." In APEX, you don't need a separate region for this—Interactive Reports have a built-in charting engine that allows users to toggle between a grid and a visual summary.

Follow these steps to add a chart showing Total Compensation by Department.

Step 1: Ensure your SQL is ready

For a chart to work well, you need at least one Label (usually a string like Department) and one Value (a number like Salary). We will use the query from our previous step.

  1. In Page Designer, verify your SQL query:

SQL
SELECT 
    e.ename AS employee_name,
    e.sal AS salary,
    e.comm AS commission,
    (e.sal + NVL(e.comm, 0)) AS total_comp,
    d.dname AS department_name
FROM emp e
JOIN dept d ON e.deptno = d.deptno

Step 2: Create the Chart (End-User View)

In an Interactive Report, charts are usually created in the browser so users can customize them.

  1. Run your page.

  2. Click the Actions button in the report search bar.

  3. Select Chart.

  4. Configure the Chart settings:

    • Chart Type: Bar or Pie (Bar is usually better for salary data).

    • Label: Department Name.

    • Value: Total Comp.

    • Function: Sum (This adds up all employee pay within each department).

    • Orientation: Vertical.

  5. Click Apply.

Step 3: Save the Chart as a "Default"

By default, the chart you just made will disappear when you refresh the page. To make it stick for everyone, you must save it as a "Public Report."

  1. Click Actions > Report > Save Report.

  2. In the Save dropdown, select As Named Report.

  3. Name: Department Pay Summary.

  4. Type: Public (Note: You need developer privileges to see the "Public" option).

  5. Click Apply.

Step 4: Toggle between Report and Chart

Now, at the top of your report (next to the search bar), you will see two new icons:

  • Grid Icon: Takes you back to the list of employees.

  • Chart Icon: Switches to the visual bars you just created.

Pro-Tip: Adding a Second Chart

You can add multiple charts! For example, you could add a Pie Chart showing the count of employees per Job Title. Users can then switch between the "Department Pay" chart and the "Staffing" chart using the drop-down menu that appears in the report settings area. 

Comments

Post a Comment