BIP Reports - Chapter 10: Using Excel Templates in BI Publisher (Global HR Use Case)

 

📊 Chapter 10: Using Excel Templates in BI Publisher (Global HR Use Case)

While RTF templates are ideal for formal, printable layouts, Excel templates are perfect for:

• Data-heavy reports

• Tabular summaries

• Dashboards with charts and pivot tables

In this chapter, you’ll learn to build Excel templates for BI Publisher using Global HR data, such as a report of active employees by business unit.

---

✅ 1. When to Use Excel Templates

Use Case	Reason to Use Excel
Data summaries	Quick filtering, pivoting, analysis
Headcount reports	Easy to update and chart
Dashboards	Built-in Excel charts and formulas
Complex calculations	Leverage Excel’s native functions

---

✅ 2. Sample Use Case: Headcount by Business Unit

📘 Data Model SQL:

 
SELECT
    bu.name AS business_unit,
    dep.department_name,
    per.full_name,
    per.person_number,
    per.hire_date,
    job.job_name
FROM
    per_all_people_f per
JOIN
    per_all_assignments_f assign ON per.person_id = assign.person_id
JOIN
    hr_departments dep ON assign.department_id = dep.department_id
JOIN
    hr_jobs job ON assign.job_id = job.job_id
JOIN
    hr_organization_units bu ON assign.business_unit_id = bu.organization_id
WHERE
    TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
  AND per.current_employee_flag = 'Y'

---

✅ 3. Steps to Create an Excel Template

🧩 Step 1: Download Sample XML

From the data model:

• Run the query

• Click Export > Save As Sample Data

📄 Step 2: Open Excel & Load XML

1. Open a blank Excel workbook

2. Go to BI Publisher > Load XML Data

3. Load the sample_data.xml

📋 Step 3: Insert Table Using BI Publisher Add-in

1. Click Insert > Table Wizard

2. Group by: business_unit

3. Add fields: department_name, full_name, person_number, job_name, hire_date

4. Customize formatting (colors, fonts, etc.)

---

✅ 4. Add Totals and Formulas

Use Excel formulas for summaries:

excel
=COUNTIF(E2:E100, "<>")   // Count employees
=AVERAGEIFS(...)          // Average tenure

You can also use:

• Subtotal per business unit

• Calculated columns (e.g., tenure = TODAY() - hire_date)

---

✅ 5. Insert Pivot Table and Charts

📊 Add Pivot Table:

1. Select full data table

2. Insert → Pivot Table

3. Group by:

   • Rows: Business Unit

   • Values: Count of Person Number (i.e., employee count)

📈 Add a Chart:

• Insert a bar chart showing headcount per BU

• Format it with dynamic titles

---

✅ 6. Use BI Publisher Syntax in Excel

• Use <?for-each:DATA_DS?> in a cell to begin row iteration

• Use <?end for-each?> to close

• Use <?field_name?> to insert data

🔹 Example in Excel Cell:

xml
<?for-each:DATA_DS?>
<?business_unit?>	<?department_name?>	<?full_name?>	<?job_name?>	<?hire_date?>
<?end for-each?>

---

✅ 7. Upload Excel Template to BI Publisher

1. Go to BI Catalog > Open Report

2. Click Edit > Layout

3. Click + > Upload Excel Template

4. Set output format: Excel (XLSX)

5. Save and test the report

---

✅ 8. Advanced: Dynamic Sheet Tabs

To create separate tabs per business unit:

1. Use this syntax in cell A1 of each sheet:

   xml
   <?for-each-group:DATA_DS;business_unit?>
   

2. Use:

   xml
   <?split-by-sheet:business_unit?>
   

3. Each sheet will be named after the business unit

---

✅ 9. Summary

Feature	Benefit
Excel layout	Ideal for HR data summaries
Pivot tables	Quick grouping and slicing
Charts	Visual headcount trends
Dynamic sheets	One sheet per business unit
BI syntax in Excel	Same as RTF: for-each, if, etc.

---

📌 Next Chapter Preview:

Chapter 11: Creating Parameterized Reports

• Add prompts for department, BU, job

• Use LOVs (List of Values)

• Cascading and default values

---