BIP Reports - Chapter 20: Frequently Asked Questions & Common Issues in Oracle Fusion BI Publisher (Global HR)

 

❓ Chapter 20: Frequently Asked Questions & Common Issues in Oracle Fusion BI Publisher (Global HR)

This chapter addresses real-world problems and FAQs commonly faced by HR teams, developers, and report users working with BI Publisher in Oracle Fusion. Each issue includes causes, solutions, and best practices.

---

🔧 1. Why is my report returning no data?

✅ Possible Causes:

• Date filters not matching data (effective_start_date / end_date)

• Incorrect join conditions (e.g., person_id, assignment_id)

• Filters applied too early or too restrictively

🛠️ Solution:

• Add TRUNC(SYSDATE) between effective_start_date and end_date

• Use outer joins if optional data is expected

sql
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date

---

🔧 2. Why is my report timing out or slow?

✅ Possible Causes:

• SELECT * usage

• Missing filters (especially on date and business unit)

• Too many nested groups in RTF

• Report returning excessive rows (e.g., all employees)

🛠️ Solution:

• Select only needed columns

• Use mandatory prompts (e.g., BU, date range)

• Apply pagination and avoid complex table structures

• Use Excel layout for large data exports

---

🔧 3. How do I display only active employees?

✅ Use:

WHERE trunc(sysdate) BETWEEN ppf.effective_start_date
AND
ppf.effective_end_date
AND
paf.assignment_status_type = 'ACTIVE'
AND
paf.primary_flag = 'Y'

🔍 Tip: Always validate the logic using Fusion tables per_all_people_f and per_all_assignments_f.

---

🔧 4. How do I remove blank pages in RTF reports?

✅ Common Causes:

• Unnecessary page breaks

• Extra paragraph marks

• Incorrect section breaks

🛠️ Solution:

• Turn on paragraph marks in MS Word (¶)

• Use <?end for-each?> and <?split-by-page-break:'Y'?> carefully

• Avoid empty rows and hidden tables

---

🔧 5. How can I group records by manager or department?

Use BI Publisher's group by functionality in RTF:

xml
<?for-each-group:ROW;./MANAGER_NAME?>
<?MANAGER_NAME?>
<?for-each:current-group()?>
    - <?EMPLOYEE_NAME?>
<?end for-each?>
<?end for-each-group?>

Or, use pivot/grouping in Excel layouts.

---

🔧 6. Why are email bursts not being sent?

✅ Possible Causes:

• Incorrect bursting SQL (missing email address)

• Invalid user email or empty value

• SMTP settings not configured properly in environment

🛠️ Solution:

• Validate bursting query returns:

sql
SELECT 'email' AS delivery_type, 'text/html' AS output_format,
       ppf.email_address AS email_address, ...

• Test with single user first

• Contact Fusion admin to verify outbound email settings

---

🔧 7. Why are date prompts showing weird format (MM/DD/YYYY vs DD-MON-YYYY)?

🛠️ Solution:

• Use Oracle date format functions:

sql
TO_CHAR(paf.actual_termination_date, 'DD-MON-YYYY')

• Ensure Fusion instance locale settings match regional needs

---

🔧 8. Can I show previous assignment/job/department in a report?

Yes — use per_periods_of_service and per_assignment_history tables, or query multiple rows from per_all_assignments_f and order by effective_start_date DESC.

---

🔧 9. How can I track changes in employee data?

Use Oracle-delivered Audit tables or create comparison reports:

• per_audit_data, per_audit_trail

• Compare current vs. previous values using assignment history

---

🔧 10. Can I hide sections in RTF based on parameter or condition?

Yes — use conditional region syntax in RTF:

xml
<?if:P_BUSINESS_UNIT='1001'?>
    Show this section
<?end if?>

✅ Useful for hiding sensitive blocks when running the same report for multiple audiences.

---

🔧 11. How can I test my SQL outside Fusion?

Use SQL Developer or Toad with a copy of Fusion’s data structure, but note:

• Some custom functions (fnd_global.user_id) may not work outside Fusion

• Dummy test data may be required

---

🔧 12. Common Table Aliases Cheat Sheet (Global HR)

Table	Alias	Purpose
per_all_people_f	ppf	Core employee/person details
per_all_assignments_f	paf	Job, org, BU, department
hr_organization_units	bu	Business Unit details
hr_jobs	job	Job names
hr_departments	dept	Department names
per_periods_of_service	ps	Employment period & termination info

---

🧠 Final Tips

• Start simple: Build a working data model, test with parameters, then move to layout

• Always validate SQL in Fusion to avoid joins returning blank

• Version control every report before editing

• Keep a changelog of each report per environment

• Know when to use OTBI vs. BI Publisher