Blog / Oracle Application Blog
Excel Template for BI Publisher Report
This blog explains
the process and steps to develop BI data templates with excel.
Steps in Brief:
- Create
xml data template. - Create
concurrent program with executable
XDODTEXE. - Create Data definition and load the data
template. - Obtain XML data from your data
model. - Design layout in excel by using XML data.
- Create the
data template and load the designed excel template. - Submit
your concurrent program and check the excel output.
About Data Template
A data
template is a simple xml file with a defined number of elements. (Refer the below Image 0.1 for sample data
template structure.)
A basic
data template contains the data Template
element where you define the name of your template and a description.
data template contains the data Template
element where you define the name of your template and a description.
The next
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.
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.
The data Query block contains the sql Statement
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 :-)).
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.
Data Template Structure
Image 0.1
Once the data
template is prepared save it with .xml format (ex: BI_excel_demo2.xml).
template is prepared save it with .xml format (ex: BI_excel_demo2.xml).
Concurrent Program
Create the
concurrent program with executable XDODTEXE.
concurrent program with executable XDODTEXE.
What is XDODTEXE?
XDODTEXE is java concurrent program used as a BI Publisher
Data template Executable.
Data template Executable.
The main purpose of this executable is to identify data
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).
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).
This executable will be used by all the BI Publisher reports (Concurrent
Program) which are using Data Template to generate the xml data.
Program) which are using Data Template to generate the xml data.
Data Definition
Data definition code
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.
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.
About Excel Templates
An Excel template is a report layout designed in Microsoft
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.
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.
The BI Publisher Tab in Excel
Features of Excel Templates
With Excel templates you can:
-
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
Steps to
Develop Excel templates
Develop Excel templates
- Obtain sample XML data from your data
model. - 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.
Creating
the XDO_METADATA Sheet
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.
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.
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.
Create a
new sheet in your Excel Workbook and name it “XDO_METADATA”.
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:
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
Design the excel
layout using xml generated tags. See below for example.
layout using xml generated tags. See below for example.
Data Template
Create the data template and load the excel layout.
Finally
submit your concurrent program and you should able to see the excel output
file as below.
submit your concurrent program and you should able to see the excel output
file as below.
