Creating Excel Templates

An Excel template is a report layout that you design in Microsoft Excel for retrieving and formatting your reporting data in Excel.
Prerequisites
Following are prerequisites for designing Excel templates:
1.Microsoft Excel 2003 or later. The template file must be saved as Excel 97-2003 Workbook binary format (*.xls).
2.To use some of the advanced features, the report designer will need knowledge of XSL and XSLT.
3.The report data model has been created.

Design the layout in Excel

First create a data model and export the xml to your local directory.
Let assume the xml structure like below

Open a new excel file and save it with (Excel 97-2003 .xls) extension.
• Create a new sheet in your Excel Workbook and name it "XDO_METADATA".
• Create the header section by entering the following variable names in column A, one per row, starting with row 1:
Version
ARU-dbdrv
Extractor Version
Template Code
Template Type
Preprocess XSLT File
Last Modified Date
Last Modified By
• Skip a row and enter "Data Constraints:" in column A of row 10.
• In the header region, for the variable "Template Type" enter the value: TYPE_EXCEL_TEMPLATE


Create one more sheet and insert a table as like below and enter the NAME BOX entry as XDO_?MALE? for the male count in the table, here MALE represents the xml tag in our data model.

Applying a Defined Name to a Cell
1. Click the cell in the Excel worksheet.
2. Click the Name box at the left end of the formula bar. The default name will display in the Name box. By default, all cells are named according to position, for example: C6.
3. In the Name box, enter the name using the XDO_ prefix and the tag name from your data. For example: XDO_?MALE?
4. Press Enter.


Similarly do it for female as well by giving XDO_?FEMALE?
For the Total cell use the native excel function to sum up the above two values by giving the formula as =SUM(C6:C7)
For calculating percentages also we can use excel native functionality.
Note:
BI Publisher defined names
To code this design as a template, mark up the cells with the XDO_ defined names to map them to data elements. The cells must be named according to the following format:
Data elements: XDO_?element_name?
where
XDO_ is the required prefix and
?element_name? is either:
the XML tag name from your data delimited by "?"
a unique name that you will use to map a derived value to the cell
For example: XDO_?MALE?
Data groups: XDO_GROUP_?group_name?
where
XDO_GROUP_ is the required prefix and
?group_name? is the XML tag name for the parent element in your XML data delimited by "?".
a unique name that you will use to define a derived grouping logic
For example: XDO_GROUP_?DEMO?
Note that the question mark delimiter, the group_name, and the element_name are case sensitive.

Creating Charts:
We can use the excel charts to embed graphs in the template, To do this just select the required cells which you want to show in chart and goto insert tab then click on any chart type ,here I am clicking PIE chart


Upload this template to BI Publisher and run the report then we will get the output like


Note:About the XDO_METADATA Sheet
Each Excel template requires a sheet within the template workbook called "XDO_METADATA". Use this sheet to identify your template to BI Publisher as an Excel template. This sheet is also used to specify calculations and processing instructions to perform on fields or groups in the template. BI Publisher provides a set of functions to provide specific report features. Other formatting and calculations can be expressed in XSLT.
It is recommended that you hide the XDO_METADATA sheet before you upload your completed template to the BI Publisher catalog. This will prevent report consumers from seeing it in the final report output.