Do you know how to generate output using Excel Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using Excel Templates in Fusion BI Publisher

I am going to show this using sample data. This is my sample data

Click "Export" button to download the XML file.

This is my XML File

Open a blank Excel file and save it as type "Excel 97-2003 Workbook" (.xls)

I have created 3 tabs "Summary", "Detail" and "XDO_METADATA"

XDO_METADATA tab is mandatory. Place the below text in XDO_METADATA tab

Version
ARU-dbdrv
Extractor Version
Template Code
Template Type	TYPE_EXCEL_TEMPLATE
Preprocess XSLT File
Last Modified Date
Last Modified By
Data Constraints:

In the "Detail" tab create Header row as shown below

Now add the XDO tags

Select the blank "cell" below the header column "Emplid". Go to "Formulas" tab and select "Define Name".

Enter the Name as "XDO_?tag name?" (ex: XDO_?EMPLID?)

Select the excel tab name "Detail" in the "Scope:" drop down list

Add any meaningful comments in the comment box

Added XDO tag is visible as shown below

Similarly add the XDO tags for other Columns

Once completed, select all the "cells" and add XDO Group tag as shown below. 

"G_1" is the tag repeating for each and every employee. So use G_1 as the group name.

XDO group tag is XDO_GROUP_?tag name? (ex: XDO_GROUP_?G_1?). XDO group tag must be added to fetch all the employees.

Go to XDO_METADATA tab and below the "Data Constraints:" define the XDO tags to count the Department, Position and Grade

Go to Summary tab and add the XDO tags (defined in the XDO_MATADATA tab) to display the count

Similarly added the XDO tags for remaining Departments, Positions and Grades. Once Completed hide the XDO_METADATA tab and save the Excel Template

Save the Data Model, Click "Save As Sample Data" button and Create a report based on the Data Model.
Upload the template as type "Excel Template" as shown below

This is my output

Summary tab:

Detail tab

I hope this blog explains how to generate Output using Excel Template. If you have any questions, please post it in the comments section.