Standard OTBI outputs are flat tables. When stakeholders need data across multiple dimensions in separate sheets — say, one sheet per department, or one sheet per absence type — BI Publisher with a correctly structured RTF template is the answer.
The Key — Native Excel Output with Group Breaks
BI Publisher supports a native Excel output format (different from HTML-rendered Excel). To enable multi-sheet output, you need two things in your RTF template:
- A
for-each-grouploop around the grouping field (e.g. Department) - A sheet-break command using the native Excel XML tag
Sheet:
Naming Each Sheet Dynamically
Use the xls:sheet tag to name each sheet after the group value:
"?>
✦ The output format in the BIP report definition must be set to Excel (*.xlsx) — not Excel 2003. The sheet-break tag only works with the XLSX output type.
Common Errors
| Error | Cause | Fix |
|---|---|---|
| All data on one sheet | Missing xls-sheet-break tag | Add tag between group iterations |
| Sheet named "Sheet1" always | Static sheet name in template | Use current-group() in xls:sheet |
| Empty sheets generated | Group field has nulls | Filter nulls in the data model query |
