Excel Template for BI Publisher Report

This blog explains the process and steps to develop BI data templates with excel.
 

Steps in Brief:

1. Create xml data template.
2. Create concurrent program with executable XDODTEXE.
3. Create Data definition and load the data template.
4. Obtain XML data from your data model.
5. Design layout in excel by using XML data.
6. Create the data template and load the designed excel template.
7. 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.

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.

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 :-)).

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).

Concurrent Program

Create the concurrent program with executable XDODTEXE.

What is XDODTEXE?

XDODTEXE is java concurrent program used as a BI Publisher 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).

 This executable will be used by all the BI Publisher reports (Concurrent 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.

 

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.

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

1.     Obtain sample XML data from your data model.
2.     Open the BlankExcelTemplate.xls file and save as your template name.
3.     Design the layout in Excel.
4.     Assign the BI Publisher defined names.
5.     Prepare the XDO_METADATA sheet.
6.     Test the template using the desktop Excel Template Builder.

Creating 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.

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:

1. Version
2. ARU-dbdrv
3. Extractor Version
4. Template Code
5. Template Type
6. Preprocess XSLT File
7. Last Modified Date
8. 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.

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.