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
<?for-each-group:EMPLOYEES;./DEPARTMENT?>
<?if:position()!=1?><?xls-sheet-break?><?end if?>
Sheet: <?current-group():DEPARTMENT?>
<?for-each:row?>
<?EMPLOYEE_NAME?> <?ABSENCE_DAYS?> <?LEAVE_TYPE?>
<?end for-each?>
<?end for-each-group?>Naming Each Sheet Dynamically
Use the xls:sheet tag to name each sheet after the group value:
<?xls:sheet name="<?current-group():DEPARTMENT?>"?>
✦ 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 |
