Organizations often needs to support diverse business requirements to generate various types of documents. Excel templates are one of the most used in business office automation. However, creating spreadsheets from scratch can be time-consuming and complex. So, it’s more convenient and efficient to build a BI Publisher report with a layout designed in Excel.

Introducing Excel Template

An Excel Template is a BI Publisher report layout designed in MS Excel. With Excel Template you can:

• Map the BI Publisher data to worksheet
• Define the format 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 Excel functionality

Requirements and prerequisites

To build an Excel Template you must have the tools:

• Oracle BI Publisher v 9.1 or later installed as stand alone or integrating with Oracle BI Enterprise Edition 9i or later.
• The Data Model must be created in BI Publisher with sample data available.
• Microsoft Office 2003 or later installed.
• Template Builder for Excel must be installed. 

When you install Template Builder for Word it also installs the plug-in for Excel. Template Builder is also known as Oracle BI Publisher Desktop.

To instal Oracle BI Publisher Desktop you must verify that BI Publisher (BIP) server installed have a certified version of BIP Desktop. Refer to product documentation to determine the appropriate version compatible with your system that should be installed. BIP Desktop should be downloaded from the Oracle Analytics Publisher site. You will get the BIPPublisher32.exe or BIPPublisher64.exe file for your Microsoft Office.

Excel Template Builder

Excel Template Builder is an extension to Microsoft Excel that facilitates the building of Excel Template for Oracle Bi Publisher. Excel Template Builder consists of the Bi Publisher menu bar, which has options and subordinate menus grouped into the following six menus: Online, Load Data, Insert, Preview, Tools and Options.

Online Commands

You can use Template Builder in connected mode or disconnected mode.

The Log On button enables a direct connection to the BI Publisher Server from your desktop Excel session. By logging in directly to BI Publisher, you can browse the catalog to choose the report to which to add the Excel Template or, if only the data model is created, you can select the data model and create the report in the catalog from your Excel session.

The Log Off button ends the connection to the BI Publisher Server.

Use the Open button to open a report from the Bi Publisher Server.

You can either use Upload Template or Upload Template As to upload the template to Bi Publisher Server catalog or view the report with button View Report.

Load Data Command

Also, you can work with Template Builder offline to create templates based on sample XML data or files. On the Load data tab, you can use Sample XML Button to load sample data.

Insert Commands

Field - Insert data field mappings to the template.

Repeating Group - Insert repeating groups mappings to the template.

Also, you use the Insert menu commands to insert tables, pivot tables, conditional format, list and so on.

Preview Command

Previews the template using the sample data or live data when in connected mode. The preview feature of Template Builder enables you to test your template before uploading it to the Bi Publisher Server. When click Excel the sample data is applied to the template and the output document is opened in a new workbook.

Tools Commands

Provides a Field Browser to review all inserted code and to edit or delete mappings. The Import command imports an Excel Analyser template and converts it to an Excel template.

Options Commands

You use the Options menu to change the locale, specify the options that influence how Template Builder generate tables and forms or the Preview functionality. Also, you can use Help button to access online Help documentation.

Building an Excel Template

To build an Excel Template:

• Step 1: Must have some sample data available.

If you work in disconnected mode

1. Obtain sample data from the report and save to a local directory.
2. On the Bi Publisher menu, click Sample XML. You are prompted to locate the sample data file in your local directory.
3. Select the sample data file and click OK. The sample XML file is loaded to the Template Builder.

If you work in connected mode

1. Log on to the BI Publisher Server.
2. On the BI Publisher menu click Open. The Open dialog launches displaying your BI Publisher report folders in the left pane.
3. Select a folder to see the reports and data models it contains in the Reports pane. Select an existing report or a data model to create a new one.

• Step 2: When the sample data is available, design the layout.

The Field command in the Insert group maps data elements from the loaded sample data to the desired location in the spreadsheet.

1. Select the cell in the spreadsheet where the data field is to display. 
2. On the BI Publisher tab, in the Insert group click Field. 
3. 

      3. Select the element in the Field dialog and click Insert. Sample data is inserted to the cell

      4. in the template. When the Template Builder inserts a field or repeating group, it creates a mapping between the data and the spreadsheet and writes the mapping to a hidden sheet called the XDO_METADATA sheet.

The Template Builder creates the hidden XDO_METADATA sheet the first time you insert a field or repeating group. To view or update the XDO_METADATA sheet unhide the sheet. 

If you have some elements that you want to repeat, you must select these cells in the spreadsheet and click Repeating Group and enter the appropriate fields in the Bi Publisher dialog.

Select the element that for each occurrence, you want the loop to repeat. When you select the For Each data field you are telling BI Publisher that for each occurrence of the selected field in the data you want the elements and processing instructions contained within the loop to be repeated.

From the list Group By, select a field by which you want to group the data. To create a simple loop, do not select a group by element. Selecting a group by element regroups the data into a new hierarchy based on the group by element. When creating a nested for-each loop, select On Grouping to repeat the for-each loop only when the element appears within the parent loop.  When you have completed the dialog options, an Excel defined name is assigned to the cell range using the BI Publisher syntax XDO_GROUP_?name? and the code is written to the XDO_METADATA sheet.

• Step 3: Test the Template

On the BI Publisher tab in the Preview group, click Excel. The sample data is applied to the template and the output document is opened in a new workbook.
 

If you would like more information about building Excel templates for Oracle Analytics Publisher or other subjects regarding these templates such as:

• Split hierarchical data across multiple sheets and dynamically name the sheets
• Insert the calculated field
• Formating Dates

We would be glad to help!  You can reach us at info@renaps.com