BIP Reports - Chapter 14: Creating Interactive BI Dashboards & Drill-Down Reports (Global HR Focus)

 

🧭 Chapter 14: Creating Interactive BI Dashboards & Drill-Down Reports (Global HR Focus)

This chapter shows you how to:

• Create clickable reports (drill-downs)

• Build interactive dashboards using BI Publisher or OTBI

• Link high-level summaries to detailed employee data

• Embed BI Publisher reports into Fusion Apps navigation

---

✅ 1. What Are Drill-Down Reports?

Drill-downs allow users to click on summary data (e.g., department name, job title) and view detailed records (e.g., employees in that department).

---

✅ 2. Use Case Example: HR Headcount Summary by Department

• Level 1: Total employees per department

• Level 2: Click → List of employees in selected department

---

✅ 3. Create Parent Report (Summary Level)

sql
SELECT
    d.department_name,
    COUNT(DISTINCT per.person_id) AS headcount,
    d.department_id
FROM
    per_all_people_f per
JOIN
    per_all_assignments_f assign ON per.person_id = assign.person_id
JOIN
    hr_departments d ON assign.department_id = d.department_id
WHERE
    per.current_employee_flag = 'Y'
GROUP BY
    d.department_name, d.department_id

---

✅ 4. Create Child Report (Detail Level)

sql
SELECT
    per.full_name,
    per.person_number,
    job.job_name,
    assign.assignment_status_type,
    d.department_name
FROM
    per_all_people_f per
JOIN
    per_all_assignments_f assign ON per.person_id = assign.person_id
JOIN
    hr_departments d ON assign.department_id = d.department_id
JOIN
    hr_jobs job ON assign.job_id = job.job_id
WHERE
    d.department_id = :P_DEPT_ID

🔗 Parameter :P_DEPT_ID is passed from the parent report via hyperlink.

---

✅ 5. Link Parent to Child (Drill-Down Setup)

In the RTF or Excel layout of the parent report:

1. Highlight the department name field

2. Add hyperlink:

xml
<?url:xdoxslt:escape_url('https://<BI-PATH>/xmlpserver/HR_Detail_Report.xdo?_xpf=1&_xpt=0&_paramsP_DEPT_ID=' || department_id)?>

• Replace <BI-PATH> with your environment URL

• Replace HR_Detail_Report.xdo with your actual child report path

Now, clicking a department name opens the detailed employee list!

---

✅ 6. Create Interactive Dashboards in Fusion

🔹 Option 1: OTBI Dashboard

If your report can be recreated in OTBI:

• Use Prompts for dynamic filters (Job, BU, Department)

• Use Sections and Views for layout

• Add BI Publisher reports as embedded content

🔹 Option 2: BI Publisher Portal Dashboard

You can:

• Create a folder in BI Catalog

• Add multiple reports to a single dashboard-like HTML page

• Use HTML formatting or Excel layout with dashboard buttons

---

✅ 7. Using Action Links (Advanced Drill-Down)

If using OTBI:

• Use Action Links to link BI Publisher reports or Fusion pages

• Example: Click Job Title → Navigate to Employee Assignment UI in Fusion

You can link to Fusion Navigator URLs like:

url
https://<instance>.oraclecloud.com/hcmUI/faces/FuseWelcome

---

✅ 8. Embed Reports in Fusion Apps

Fusion allows embedding BI Publisher reports directly into:

• Navigator menus

• Infolets / SmartNav

• Transactional pages via Page Composer

🔐 Make sure report security is properly set via roles and folders.

---

✅ 9. Summary: Tips for Interactive Reporting

Tip	Benefit
Use consistent parameters in parent/child	Smooth drill-down
Use friendly report names	Manager-friendly navigation
Filter child reports by input	Prevent data overload
Embed directly in Fusion	Improves accessibility
Test links across roles	Ensure security coverage

---

📌 Next Chapter Preview:

Chapter 15: Securing BI Reports and Managing Roles

• Control access by role (HRBP, Manager, etc.)

• Restrict report data dynamically

• Use row-level security with Fusion roles