BIP Reports - Chapter 6: Designing RTF Templates Using Word and Template Builder (Global HR Example)

 

📝 Chapter 6: Designing RTF Templates Using Word and Template Builder (Global HR Example)

In this chapter, you'll learn how to use Microsoft Word with BI Publisher Template Builder to create professional report layouts. We'll design an RTF layout using HR data—specifically, Employee Details by Department.

---

✅ 1. Prerequisites

Before starting, ensure:

• Microsoft Word is installed

• BI Publisher Desktop Plugin (Template Builder) is installed

  • Download from: https://www.oracle.com/middleware/technologies/bi-publisher-downloads.html

• You’ve created a Data Model (from Chapter 5) with Global HR data

---

✅ 2. Example Data Model: Employee Details

This example assumes you have a data model (Employee_Details_DM) with the following SQL:

SELECT
    per.full_name,
    per.person_number,
    per.email_address,
    per.phone_number,
    job.job_name,
    pos.position_name,
    dep.department_name,
    per.hire_date
FROM
    per_all_people_f per
JOIN
    per_all_assignments_f assign ON per.person_id = assign.person_id
JOIN
    hr_jobs job ON assign.job_id = job.job_id
JOIN
    hr_positions pos ON assign.position_id = pos.position_id
JOIN
    hr_departments dep ON assign.department_id = dep.department_id
WHERE
    TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN assign.effective_start_date AND assign.effective_end_date
ORDER BY
    dep.department_name, per.full_name

---

✅ 3. Download Sample XML for Layout Design

1. Open the data model in BI Publisher.

2. Go to the Data tab.

3. Click View to execute.

4. Once results appear, click Export > Save As Sample Data.

5. This will download an .xml file — save it locally (e.g., EmployeeSample.xml)

---

✅ 4. Designing the RTF Layout in Word

🧩 Step-by-Step:

1. Open Microsoft Word

2. Click on the BI Publisher tab

3. Click Load XML Data → Load EmployeeSample.xml

4. Click Insert > Table Wizard

   • Data Source: /DATA_DS

   • Group by: department_name

   • Columns:

     • full_name, person_number, job_name, position_name, email_address, phone_number, hire_date

5. Choose formatting options (borders, shading, etc.)

6. Add page headers, footer with date, and logo if needed

✨ Optional Enhancements:

• Use Conditional Formatting:

  • Example: Highlight rows where hire_date < SYSDATE - 365 (tenure over a year)

• Add totals/counts using BI Publisher > Insert > Function

---

✅ 5. Preview and Test the Layout

1. Click Preview in Word (choose PDF or HTML)

2. Ensure the data populates correctly and the formatting looks good

3. Save the file as: Employee_Details_Template.rtf

---

✅ 6. Upload RTF Layout to Report Definition

1. Go to Catalog > Open your report

2. Click Edit Report

3. In the Layout section, click + > Upload RTF

4. Name it: Employee Details Layout

5. Select output formats: PDF, Excel, RTF

6. Save and run the report to validate

---

✅ 7. Summary

Task	Tool Used
Design Layout	Word + Template Builder
Load Data	Sample XML from BIP
Format & Group Data	Table Wizard
Upload to Fusion	Report Editor

With this, you've created a dynamic, styled RTF layout for a Global HR report!

---

🔹 Next Steps

• Previous Chapter: Chapter 5: Creating Data Models from Scratch

• Next Chapter:  Chapter 7:  Advanced RTF Layout Techniques (Global HR Examples)

---

📌 Next Chapter Preview:

Chapter 7: Advanced Layout Techniques (Conditional Logic, Totals, Barcodes)

• Apply IF conditions

• Use for-each loops

• Insert images, barcodes, dynamic tables