The Excel Template Builder facilitates template design by automating the insertion of simple mappings, providing preview functionality, and enabling direct connection to the BI Publisher server from your Excel session.
The BI Publisher tab that displays when you install the Template Builder is shown in the following illustration.
You can use the Template Builder in connected mode or disconnected mode. In connected mode, log in to the BI Publisher server from Excel. The connection enables you to browse the BI Publisher catalog and load sample data from an existing report or data model. When your template is complete, you can upload it directly to the report definition in the BI Publisher catalog. In disconnected mode, you must download a sample data file from the data model to your local client.
This section includes the following topics about using the Template Builder for Excel:
In connected mode you can interact directly with the BI Publisher server.
The process flow for creating or editing a template in connected mode is:
The following sections provide more detail on the Template Builder features when working in connected mode:
The Excel Template Builder 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 no report has been created, you can select the data model and create the report in the catalog from your Excel session.
To log on to the BI Publisher server:
After logging in, the following commands in the Online group become enabled.
Log Off - ends the connection to the BI Publisher server.
Open - enables interaction with the BI Publisher catalog. See Accessing the BI Publisher Catalog from the Template Builder.
View Report - executes the data model on the server and returns live results to view in your template. If the data model includes parameters, you are prompted to enter values.
Upload Template/Upload Template As - uploads the template to the BI Publisher catalog. See Uploading Templates from the Template Builder.
The Open online command enables interaction with the BI Publisher catalog.
The Open command launches the Open Template dialog to enable access to the BI Publisher catalog.
Navigate the catalog folders to locate the report, data model, or existing layout template. From this dialog you can initiate one of the following actions:
Modify an existing Excel template.
When you select a report in the Reports region, any existing Excel templates or Excel Analyzer templates (deprecated) are displayed in the lower Layout Templates region. To modify an existing template, select the template name and click Open Layout Template. The Template Builder loads the sample data from the report's data model and opens the existing template in Excel.
Create a new template for an existing report.
Select the report name in the Reports region and click Open Report. The Template Builder loads the sample data for this report's data model.
Select a data model to create a new report.
When you select a data model from the catalog, the Create Report button is enabled. Click Create Report and you are prompted to enter a report name and select the location in the catalog to save the new report.
A link to upload templates is provided if you are online with the server.
If you have maintained the connection during the design process, click one of the following to upload your completed template to the BI Publisher server:
Upload Template uploads your edited template and replaces the existing template in the catalog. Upload Template is enabled only when you have opened an existing template from the Open Template dialog using the Open Layout Template button.
Upload Template As prompts you to assign a Template Name and Locale to the template then uploads the file to the report in the BI Publisher catalog.
When direct connection to the BI Publisher server is not possible or not desirable, you can use the Template Builder to design and preview templates in disconnected mode.
In disconnected mode the commands in the Online group are not enabled. The process flow for working in disconnected mode is:
The following sections provide more detail on the Template Builder features when working in disconnected mode:
The Template Builder requires sample data to insert the data field mappings to your template.
For information on saving sample data from the report data model, see Testing Data Models and Generating Sample Data in Data Modeling Guide for Oracle Business Intelligence Publisher.
If you do not have access to the report data model, but you can access the report, then you can alternatively save sample data from the report viewer.
To save data from the report viewer:
You can load sample data into a local directory while offline.
Once you have saved the sample data from the report data model to a local directory, load it to the Template Builder.
You can upload report templates while offline.
When working in disconnected mode, upload the template to the report editor following the instructions in Adding a Layout by Uploading a Template File.
The Field command in the Insert group maps data elements from the loaded sample data to the desired location in the spreadsheet.
To insert a field:
When you insert a field, the Template Builder creates a mapping between the data and the cell by assigning a unique Excel defined name to the cell and mapping the data element to that defined name. The mapping is written to the XDO_METADATA sheet as shown in the following illustration.
Note that the XDO_METADATA sheet is hidden by default.
The Field dialog provides the following features.
Find
For an XML document with a large and complicated structure, use the find functionality to find a specific field. Enter all or part of the field name into the Find field and click Find Next.
Business View or XML Tag Name View
When working in connected mode, you can choose whether to view the data structure using the Business View names or the XML Tag Names as defined in the data model. Business View names are user-friendly names defined for the data elements in the data model editor. This option is not available when sample data has been loaded from a locally stored file or when the data model does not include Business View names.
You can insert repeating groups of cell elements.
To insert a repeating group:
The Field Browser enables you to view and edit the code inserted by the Template Builder and the code you inserted manually into the XDO_METADATA sheet.
When you select a line of code in the Field Browser, the corresponding cells in the template are highlighted, so you know which field you are editing, deleting, or viewing.
To edit or delete a field using the Field Browser:
Use the preview feature of the Template Builder to test your template before uploading it to the BI Publisher server.
To preview a template with the loaded sample data:
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 are working in connected mode, you have the option of testing your template with live data from the report data model using View Report.
To view your template using live data:
On the BI Publisher tab in the Online group, click View Report.
The Template Builder sends a request to execute the data model on the BI Publisher server and returns the data to apply to the template. If the data model requires parameters, you are prompted to enter values. The output document is opened in a new Excel workbook.
The Excel Analyzer feature of BI Publisher has been deprecated, but if you have Excel Analyzer templates from previous BI Publisher releases, you can use the Import command of the Excel Template Builder to import an Excel Analyzer template and convert it to an Excel template. The Import command supports only Excel Analyzer templates created using the Offline Mode.
The Template Builder converts the Excel Analyzer template to an Excel template.