Oracle APEX Part 8 - Interactive Reports Simple with computed columns of Salary

 To create an Interactive Report that displays Employees and their Departments, the most efficient way is to use a SQL JOIN. This allows you to show the department name instead of just a numeric ID, making the report much more readable.

Here are the detailed steps to build this in Oracle APEX.

Step 1: Prepare the SQL Query

First, you need a query that pulls data from both tables. We will use a LEFT JOIN to ensure employees appear even if they aren't assigned to a department yet.

SQL
SELECT 
    e.empno,
    e.ename AS employee_name,
    e.job,
    e.hiredate,
    e.sal AS salary,
    d.dname AS department_name,
    d.loc AS location,
 (e.sal + NVL(e.comm, 0)) AS total_compensation,
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno

Step 2: Create the Report Page

  1. Log in to your App Builder and select your application.

  2. Click Create Page.

  3. Select Component and then choose Report.

  4. Select Interactive Report.

  5. Page Attributes: Give your page a name (e.g., "Employee Directory").

  6. Source:

    • Location: Local Database

    • Type: SQL Query

    • SQL Query: Paste the SQL code from Step 1.

  7. Click Create Page.

Step 3: Enhance the UI (Page Designer)

Once the page is created, use Page Designer to make it look professional:

1. Format the Salary (Currency)

  • In the Left Pane (Rendering Tree), expand Columns.

  • Select the SALARY column.

  • In the Right Pane (Attributes), find Appearance > Format Mask.

  • Choose a currency format like $999G999G999G999G990D00.

  • Select TOTAL_COMPENSATION under the Columns folder.

  • In the Appearance section, set the Format Mask to a currency style (e.g., $999G999G999G990D00).

  • (Optional) Change the Heading to "Total Comp".

2. Creating a "Virtual" Column (Alternative)

If you don't want to change your SQL query, you can also create a Computation directly within the Interactive Report as an end-user:

  1. Run the page.

  2. Click Actions > Data > Compute.

  3. Column Label: Total Pay

  4. Format Mask: $5,249.10

  5. Expression: I + J (where I is Salary and J is Commission).

  6. Click Apply.

3. Add a Highlight for High Earners

You can pre-configure the report to highlight certain data:

  • Run the page.

  • Click the Actions button on the report.

  • Select Format > Highlight.

  • Name: High Salary

  • Background Color: Light Green

  • Condition: Salary > 3000

  • Click Apply.

Step 4: Add "Breadcrumbs" or Links

If you want users to click an employee name to see more details:

  1. In Page Designer, select the EMPLOYEE_NAME column.

  2. Change Identification > Type to Link.

  3. Under Target, click No Link Defined.

  4. Set the Target to a "Form" page you’ve created for employees, passing the EMPNO as a parameter.

Summary of Benefits

By using an Interactive Report for this:

  • Users can group by DEPARTMENT_NAME to see a breakdown of staff.

  • Managers can filter by JOB (e.g., "CLERK" or "MANAGER").

  • Everyone can export the list to Excel or PDF using the Actions > Download menu.

Comments

Post a Comment