Excel Template for BI Publisher Report
This blog explains
the process and steps to develop BI data templates with excel.
xml data template.
concurrent program with executable
- Create Data definition and load the data
- Obtain XML data from your data
- Design layout in excel by using XML data.
- Create the
data template and load the designed excel template.
your concurrent program and check the excel output.
template is a simple xml file with a defined number of elements. (Refer the below Image 0.1 for sample data
data template contains the data Template
element where you define the name of your template and a description.
element set is the parameters
section. In this section you define the names, data types and possible default
values for parameters used in your query. You define these parameters later
also in the concurrent program definition.
block where you define the SQL query (in conjunction with any parameters).
Optionally you are able to run some trigger at a stage of the data generation
(quite handy sometimes :-)).
The data Structure block defines the output
columns of the data. When defining the xml file always make sure you close
every element block.
template is prepared save it with .xml format (ex: BI_excel_demo2.xml).
concurrent program with executable XDODTEXE.
Data template Executable.
template file (.xml) and execute the data template to generate the raw (output)
xml data, which is later used by BI Publisher formatting engine to format as
per the layout (RTF, PDF, etc).
Program) which are using Data Template to generate the xml data.
should be same as your concurrent program short name. Load the data template xml file by clicking on the upload option next to the Data template.
Excel for formatting your enterprise reporting data in Excel spreadsheets.
Excel templates provide a set of special features for mapping data to
worksheets and for performing additional processing to control how the data is
output to excel workbooks.
Define the structure for the data in Excel output
Split hierarchical data across multiple sheets and dynamically name the sheets
Create sheets of data that have master-detail relationships
Use native XSL functions in the data to manipulate it prior to rendering
Use native Excel functionality
Develop Excel templates
- Obtain sample XML data from your data
- Open the BlankExcelTemplate.xls file
and save as your template name.
- Design the layout in Excel.
- Assign the BI Publisher defined names.
- Prepare the XDO_METADATA sheet.
- Test the template using the desktop
Excel Template Builder.
the XDO_METADATA Sheet
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.
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.
new sheet in your Excel Workbook and name it “XDO_METADATA”.
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
layout using xml generated tags. See below for example.
submit your concurrent program and you should able to see the excel output
file as below.