BIP Reports - Chapter 17: Real-World BI Publisher Reports for Global HR (With Queries & Layout Tips)

 

📊 Chapter 17: Real-World BI Publisher Reports for Global HR (With Queries & Layout Tips)

This chapter provides 5 essential HR reports, including:

• Report purpose

• SQL queries

• Recommended layout (Excel or RTF)

• Filters and bursting options

---

📝 Report 1: Active Employees by Business Unit

🔹 Purpose:

Show current headcount across BUs, used by HRBPs and leadership.

✅ SQL Query:

 
SELECT
    bu.name AS business_unit,
    COUNT(DISTINCT ppf.person_id) AS headcount
FROM
    per_all_people_f ppf
JOIN
    per_all_assignments_f paf ON ppf.person_id = paf.person_id
JOIN
    hr_organization_units bu ON paf.business_unit_id = bu.organization_id
WHERE
    SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
    AND paf.assignment_type = 'E'
    AND paf.primary_flag = 'Y'
    AND ppf.current_employee_flag = 'Y'
GROUP BY
    bu.name

🎨 Layout:

• Excel Template: Pivot table for BU vs. Headcount

• Include graphs: Bar chart showing comparison

• Optional: Drill-down to employee list (see Chapter 14)

---

📝 Report 2: Employee Details Report (For HRBP)

🔹 Purpose:

HRBP needs full employee info with filters like BU, Job, or Department.

✅ SQL Query:

 
SELECT
    ppf.full_name,
    ppf.person_number,
    bu.name AS business_unit,
    dept.department_name,
    job.job_name,
    paf.assignment_status_type
FROM
    per_all_people_f ppf
JOIN
    per_all_assignments_f paf ON ppf.person_id = paf.person_id
JOIN
    hr_organization_units bu ON paf.business_unit_id = bu.organization_id
JOIN
    hr_departments dept ON paf.department_id = dept.department_id
JOIN
    hr_jobs job ON paf.job_id = job.job_id
WHERE
    ppf.current_employee_flag = 'Y'
    AND paf.primary_flag = 'Y'

🎨 Layout:

• RTF or Excel, depending on formatting need

• Filters: Prompt for BU, department, or job

• Bursting: By BU to HRBPs

---

📝 Report 3: Employee Exit Tracker (Resigned Employees)

🔹 Purpose:

Track resigned employees with effective termination dates.

✅ SQL Query:

 
SELECT
    ppf.full_name,
    ppf.person_number,
    ppf.termination_date,
    pa.action_reason,
    bu.name AS business_unit
FROM
    per_all_people_f ppf
JOIN
    per_periods_of_service ps ON ppf.person_id = ps.person_id
JOIN
    per_actions pa ON ps.action_id = pa.action_id
JOIN
    hr_organization_units bu ON ps.business_unit_id = bu.organization_id
WHERE
    ppf.termination_date IS NOT NULL
    AND ppf.termination_date BETWEEN :P_FROM_DATE AND :P_TO_DATE

🎨 Layout:

• Excel: Include slicers for BU and month

• Useful chart: Monthly exits by BU

• Parameters: From Date, To Date

---

📝 Report 4: HR Hierarchy Report (Manager and Reportees)

🔹 Purpose:

Used by HR and managers to visualize direct/indirect reports.

✅ SQL Query:

 
SELECT
    mgr.full_name AS manager_name,
    emp.full_name AS employee_name,
    job.job_name,
    dept.department_name
FROM
    per_people_x_managers pxm
JOIN
    per_all_people_f emp ON emp.person_id = pxm.person_id
JOIN
    per_all_people_f mgr ON mgr.person_id = pxm.manager_id
JOIN
    hr_jobs job ON job.job_id = pxm.job_id
JOIN
    hr_departments dept ON dept.department_id = pxm.department_id
WHERE
    emp.current_employee_flag = 'Y'

🎨 Layout:

• RTF Template: Group by manager → list of reportees

• Add org chart graphics for visual hierarchy (optional)

---

📝 Report 5: Incomplete Personal Details (Data Audit)

🔹 Purpose:

Identify employees missing important data (email, phone, address).

✅ SQL Query:

 
SELECT
    ppf.full_name,
    ppf.person_number,
    ppf.email_address,
    ppf.phone_number,
    ppf.national_identifier
FROM
    per_all_people_f ppf
WHERE
    (ppf.email_address IS NULL
     OR ppf.phone_number IS NULL
     OR ppf.national_identifier IS NULL)
    AND ppf.current_employee_flag = 'Y'

🎨 Layout:

• Excel Template with conditional formatting (highlight blanks)

• Use in HR data cleanup drives

---

🔐 Security Tip:

Use fnd_global.user_id or :P_USER_ROLE to limit data visibility depending on who runs the report. (See Chapter 15)

---

📁 Deployment Tip:

Organize these reports in BI Catalog folders like:

 
/Custom/Human Capital/Reports/HRBP/

Set folder access via catalog permissions for HR roles only.

---

📌 Next Chapter Preview:

Chapter 18: Performance Optimization and Best Practices

• Tuning SQL for faster reports

• Optimizing BI layouts

• Caching, scheduling, and minimizing load on Fusion DB