Using the Template Builder for Excel
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:
Working in Connected Mode
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:
- Open Excel with the BI Publisher Template Builder for Excel Add-in installed.
- Log on to the BI Publisher server.
- Select the report or data model for which you want to create a new layout; or, select an existing layout to modify.
- Design your template in Excel.
- Preview your template using the View Report or Preview command.
- Use one of the upload template commands to upload your completed template to the BI Publisher catalog.
The following sections provide more detail on the Template Builder features when working in connected mode:
Logging In Through the Template Builder
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:
- In the Login dialog, enter your BI Publisher username and password and the URL to the BI Publisher server. The URL to the BI Publisher server takes the form
http:// www.<host>:<port>/xmlpserver. The Login dialog is shown in the following illustration.Note:
Log in is supported only for connections directly to the BI Publisher server. For example: http://www.example.com:7001/xmlpserver. The Template Builder does not support logging in through the Oracle BI Enterprise Edition analytics server.
Online Features of the Template Builder
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.
Accessing the BI Publisher Catalog 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.
Uploading Templates from the Template Builder
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.
Working in Disconnected Mode
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:
- Log in to the BI Publisher server and download sample data from the data model for which you want to design a template.
- Open Excel with the BI Publisher Template Builder for Excel Add-in installed.
- Load the sample data to the Template Builder.
- Design your template in Excel.
- Preview your template using the Preview command.
- Log in to the BI Publisher server and use the report editor to upload your template.
The following sections provide more detail on the Template Builder features when working in disconnected mode:
Obtaining Sample Data
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:
- In the BI Publisher catalog, navigate to the report.
- Click Open to run the report in the report viewer.
- Click the Actions menu, then click Export, then click Data. You are prompted to save the XML file.
- Save the file to a local directory.
Loading Sample Data in Disconnected Mode
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.
- Open Excel with the BI Publisher Template Builder for Excel Add-in installed.
- On the BI Publisher tab, in the Load Data group, click Sample XML. You are prompted to locate and select the data from its saved location. A confirmation message confirms the data is loaded.
Uploading Templates to the Report
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.
Inserting Fields
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:
- On the Field dialog select the element to insert to the cell. Notice that as you select items in the data structure, sample data is displayed in the Example region as shown in the following illustration.
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.
More Features of the Field Dialog
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.
Inserting Repeating Groups
You can insert repeating groups of cell elements.
To insert a repeating group:
- Enter the appropriate fields in the BI Publisher Properties dialog, as shown in the following illustration.
For Each
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.
For example, assume that the data contains invoice data for customers and you want to create a table with each customer's invoices. In this case, for each customer number you want the table to repeat. You would therefore select the customer number in the For Each field to create a new loop (or group) for each customer.
Note the following about creating repeating groups:
-
For loops and groupings not inside another group (that is, outer groups) you must select the repeating data element to be used. For example if the data set is flat, the only repeatable element is /DATA/ROWSET/ROW. In cases with multiple data sources or hierarchical XML you can choose the data set.
-
If you are creating nested groups (inserting a loop or group inside of another loop in the template), the On Grouping checkbox is selected and the For Each field is not editable because it is already defined by the preexisting outer loop. The For Each field is displayed as "Group Item" to inform you that an outer group is already defined.
Absolute Path
Select this check box to use the Absolute Path to the element in the XML structure. This is important if the data contains the same element name grouped under different parent elements.
Group By
Select a field from the list 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.
On Grouping
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. Deselect On Grouping to repeat global data (outside the parent loop) within the nested loop.
-
- When you have completed the dialog options, click OK to insert the BI Publisher code to define the groupings. 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 as shown in the following illustration.
Using the Field Browser to View, Edit, and Delete Fields
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:
- The Field Browser displays the BI Publisher commands that are present in the template. Select the field or command to view. The code for the selected command displays in the lower Edit region. Notice that if the code has opening and ending tags (such as the opening and ending tags of a repeating group) the opening tag display in the upper code box and the closing tag displays in the lower code box.
When you select a command, the area of the template that corresponds to the code is highlighted. In the following illustration the repeating group is selected in the Field Browser and the corresponding fields are highlighted in the template.
Previewing Templates
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.
Importing Excel Analyzer Templates
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.
- Open the Excel Analyzer template. If you are working in connected mode, navigate to the report that contains the template you wish to convert. When you select the report in the Open Template dialog, the Excel Analyzer template displays in the Layout Templates region as type "excel". Click Open Layout Template to open the Excel Analyzer template in Excel.
- Click Import. A message notifies you: This feature will overwrite your template.
- Click OK.
The Template Builder converts the Excel Analyzer template to an Excel template.