This chapter describes creating report templates for BI Publisher in Microsoft Excel using the Template Builder for Excel.
This chapter includes the following sections:
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.
This introduction includes the following topics:
With Excel templates you can:
The following are limitations of Excel templates:
Following are prerequisites for designing Excel templates:
Important: If you are using a version later than Excel 2003 to create your template and then save as Excel 97-2003, ensure that you do not use any features of the later version that are not supported in Excel 97-2003. For example, Excel 2003 allows only three conditional formatting rules per cell, but Excel 2007 allows more. If you apply more than three conditional formatting rules to a cell, only three are applied. Excel 2007 also provides color support not provided in Excel 2003.
Excel templates generate Excel binary (.xls) output only.
BI Publisher provides a downloadable add-in to Excel that provides the following features:
The Template Builder for Excel is installed automatically when you install the BI Publisher Desktop Tools. The tools can be downloaded from the Home page of Oracle Business Intelligence Publisher or Oracle Business Intelligence Enterprise Edition, as follows:
Under the Get Started region, click Download BI Publisher Tools , then select the BI Publisher Desktop option (32bit Office or 64bit Office) appropriate for your version of Microsoft Office.
The Excel Template Builder is not compatible with the (deprecated) Analyzer for Excel. If you have the Analyzer for Excel installed from a previous version, the BI Publisher Tools installer detects its presence and halts the installation. You must remove the Analyzer for Excel before installing the BI Publisher Desktop. The Excel Template Builder includes a feature to import Analyzer for Excel templates to the Excel template format. See Section 6.3.7, "Importing Excel Analyzer Templates."
The Template Builder installation includes sample Excel templates.
To access the samples from a Windows desktop:
When you design Excel templates use the Excel Template Builder for inserting fields and repeating groups to your 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. To add calculations or more advanced functions, enter the XSL functions directly in the XDO_METADATA sheet using the named mappings created by the Template Builder. For more information about template-data mappings, see Section 6.6, "Understanding Excel Template Concepts."
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 Figure 6-1.
Figure 6-1 The BI Publisher Tab in Excel
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:
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.
Figure 6-2 Excel Template Builder Login Dialog to BI Publisher ServerAfter logging in, the following commands in the Online group become enabled (shown in Figure 6-3).
Figure 6-3 Online Commands
The Open command launches the Open Template dialog shown in Figure 6-4 to enable access to the BI Publisher catalog.
Figure 6-4 Open Template Dialog
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:
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:
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 Oracle Fusion Middleware 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:
Once you have saved the sample data from the report data model to a local directory, load it to the Template Builder.
When working in disconnected mode, upload the template to the report editor following the instructions in Section 2.6.2, "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 Figure 6-6.
Figure 6-6 Field Mapping Inserted to XDO_METADATA Sheet
Note that the XDO_METADATA sheet is hidden by default.
The Field dialog provides the following features:
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.
To insert a repeating group:
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.
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.
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.
Figure 6-8 XDO_METADATA Sheet Showing Repeating GroupThe 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:
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:
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.
To import an Excel Analyzer template:
The Template Builder converts the Excel Analyzer template to an Excel template.
This section demonstrates the concepts of Excel templates by describing the steps to create a simple Excel template using the Excel Template Builder. This procedure follows these steps:
The method you choose for loading sample data depends on whether you are working in connected or disconnected mode.
The sample data for this example is a list of employees by department. Note that employees are grouped and listed under the department.
20 Marketing 201 Michael Hartstein MHARTSTE 515.123.5555 1996-02-17T00:00:00.000+00:00 13000 202 Pat Fay PFAY 603.123.6666 1997-08-17T00:00:00.000+00:00 6000 . .
To build the template described in this tutorial, use the sample data available in the Samples folder installed with BI Publisher Desktop. A very similar data set can be found in \BI Publisher Desktop\Template Builder for Word\samples\Excel templates\Employee By Departments\EmpByDept Single Sheets\EmpbyDeptExcelData.xml
In Excel, determine how you want to render the data and create a sample design, as shown in Figure 6-10.
Figure 6-10 A Sample Design
The design shows a department name and a row for each employee within the department. You can apply Excel formatting to the design, such as font style, shading, and alignment. Note that this layout includes a total field. The value for this field is not available in the data and requires a calculation.
In this layout design, the following fields must be mapped to the template from the data:
To insert field mappings using the Template Builder:
A group is a set of data that repeats for each occurrence of a particular element. In the sample template design, there are two groups:
In other words, the employees are "grouped" by department and each employee's data is "grouped" by the employee element. To achieve this in the final report, insert a repeating group around the cells that are to repeat for each grouping element.
Note that the data must be structured according to the groups that you want to create in the template. The structure of the data for this example
establishes the grouping desired for the report.
To insert the repeating group for Employees:
Figure 6-12 shows BI Publisher Properties definitions for the Employees group. Notice that just the row of employee data is highlighted. Do not highlight the headers. Notice also that the total cell is not highlighted.
Figure 6-12 Inserting the Repeating Group for Employees
To insert the repeating group for Departments :
Figure 6-14 shows the selections for the BI Publisher Properties dialog.
Figure 6-14 BI Publisher Properties for the Departments Repeating Group
Finally, insert the second Salary field that is to be an aggregated sum for each department.
To insert the calculated field:
Figure 6-16 Entry for the Salary Calculation
To preview a template with the loaded sample data:
Figure 6-17 A Preview of a Template with Sample Data
Excel cannot recognize canonical date format. If the date format in the XML data is in canonical format, that is, YYYY-MM-DDThh:mm:ss+HH:MM, you must apply a function to display it properly.
One option to display a date is to use the Excel REPLACE and SUBSTITUTE functions. This option retains the full date and timestamp. If you only require the date portion in the data (YYY-MM-DD), then another option is to use the DATEVALUE function. The following example shows how to use both options.
Example: Formatting a Canonical Date in Excel
Using the Employee by Department template and data from the first example, this procedure adds the HIRE_DATE element to the layout and displays the date as shown in Column E of Figure 6-18.
Figure 6-18 The Employee by Department Template Showing the Hire Date
To format the date:
Note: If you are not using the Template Builder, copy and paste a sample value for HIRE_DATE from the XML data into the cell that is to display the HIRE_DATE field. For example: Copy and paste 1996-02-03T00:00:00.000-07:00 into the E8 cell. Assign the cell the defined name XDO_?HIRE_DATE? to map it to the HIRE_DATE element in the data.
The inserted field is shown in Figure 6-19.=--REPLACE(SUBSTITUTE(E8,"T"," "),LEN(E8)-6,6,"")
=DATEVALUE(LEFT(E8,10))
Notice that in both functions, "E8" refers to the cell that contains the value to convert.
After you enter the function, it populates the F8 cell as shown in Figure 6-21.
Figure 6-21 Hire Date Cell (F8) Populated
Similar to RTF template design, Excel template design follows the paradigm of mapping fields from the XML data to positions in the Excel worksheet. Excel templates make use of features of Excel in conjunction with special BI Publisher syntax to achieve this mapping. In addition to direct mapping of data elements, Excel templates support more complex formatting instructions by defining the cell ranges and the commands in a separate worksheet designated to contain these commands. This sheet is called the XDO_METADATA sheet.
Excel templates use named cells and groups of cells to enable BI Publisher to insert data elements. Cells are named using BI Publisher syntax to establish the mapping back to the XML data. The cell names are also used to establish a mapping within the template between the named cell and calculations and formatting instructions that are defined on the XDO_METADATA sheet.
The template content and layout must correspond to the content and hierarchy of the XML data file used as input to the report. Each group of repeating elements in the template must correspond to a parent-child relationship in the XML file. If the data is not structured to match the desired layout in Excel it is possible to regroup the data using XSLT preprocessing or the grouping functions. However, for the best performance and least complexity it is recommended that the data model be designed with the report layout in mind.
For more information about grouping, see the following:
BI Publisher uses the Excel defined names feature to identify data fields and repeating elements. A defined name in Excel is a name that represents a cell, range of cells, formula, or constant value.
To learn more about defined names and their usage in Microsoft Excel 2007, see the Microsoft help topic: "Define and use names in formulas."
The Template Builder for Excel automatically creates the defined names when you use it to insert fields and repeating groups. You can also insert the defined names manually. The defined names used in the Excel template must use the syntax described in this chapter and follow the Microsoft guidelines described in the Microsoft Excel help document. Note that BI Publisher defined names are within the scope of the template sheet.
The BI Publisher defined names are Excel defined names identified by the prefix "XDO_". Creating the defined name with the BI Publisher code in the template creates the connection between the position of the code in the template and the XML data elements, and also maintains the ability to dynamically grow data ranges in the output reports, so that these data ranges can be referenced by other formula calculations, charts, and macros.
You can use the XDO_ defined names in Excel native formulas as long as the defined names are used in a simple table. When a report is generated, BI Publisher automatically adjusts the region ranges for those named regions so that the formulas calculate correctly.
However, if you create nested groups in the template, then the cells generated in the final report within the grouping can no longer be properly associated to the correct name. In this case, the use of XDO_ defined names with native Excel functions cannot be supported.
Each Excel template requires a sheet within the template workbook called "XDO_METADATA". BI Publisher uses this sheet in the template in the following ways:
As the template designer, you also use this sheet to specify more advanced 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.
When you begin the design of a new Excel template using the Template Builder, the first time you use one of the Insert functions the Template Builder automatically creates a hidden XDO_METADATA sheet. A message informs you that the sheet has been created.
BI Publisher creates the sheet as a hidden sheet. Use the Excel Unhide command to view and edit the XDO_METADATA sheet.
The XDO_METADATA sheet is created with the format shown in Figure 6-24.
Figure 6-24 Format of the XDO_METADATA Sheet
The format consists of two sections: the header section and the data constraints section. Both sections are required.
In the header section, all the entries in column A must be listed, but a value is required for only one: Template Type, as shown. The entries in Column A are:
The Data Constraints section is used to specify the data field mappings and other processing instructions. Details are provided in the following sections.
Oracle recommends that you hide the XDO_METADATA sheet before uploading the completed template to the BI Publisher catalog to prevent its inclusion in the final report output. Use the Excel Hide command to hide the sheet before uploading the template to the server.
BI Publisher provides a set of functions to achieve additional reporting functionality. You define these functions in the Data Constraints region of the XDO_METADATA sheet.
The functions make use of Columns A, B, and C in the XDO_METADATA sheet as follows:
Use Column A to declare the function or to specify the defined name of the object to which to map the results of a calculation or XSL evaluation.
Use Column B to enter the special XDO-XSL syntax to describe how to control the data constraints for the XDO function, or the XSL syntax that describes the special constraint to apply to the XDO_ named elements.
Use Column C to specify additional instructions for a few functions.
The functions are described in the following three sections:
Table 6-1 lists functions that you can add to a template using the commands shown and a combination of BI Publisher syntax and XSL. A summary list of the commands is shown in Table 6-1. See the corresponding section for details on usage.
Table 6-1 Reporting Functions
XDO_SHEET_? with XDO_SHEET_NAME_?