An Excel template is a report layout that you design in Microsoft Excel for retrieving and formatting your reporting data in Excel.
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:
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.
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?
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?
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.
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
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.