Understanding Excel Template Concepts
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.
Mapping Data Fields and Groups
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.
Note:
For more information about grouping, see the following:
Use of Excel Defined Names for Mapping
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.
Tip:
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.
Note:
When you create an Excel Template manually (that is, NOT using the BI Publisher Desktop Excel Template Builder), you must provide default values for all marked up cells XDO_?. The default values must match to the data type of the report data XML file. Without default values for the XDO_? cells, the output cells generated from those template cells may lose formatting and the result is unpredictable. If you use BI Publisher Desktop to create an Excel Template, the default values are automatically supplied with the first row of sample data in the report data file.Use of "XDO_" Prefix to Create Defined Names
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.
Using Native Excel Functions with the "XDO_" Defined Names
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.
About the XDO_METADATA Sheet
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:
-
To identify the template as an Excel template.
-
To insert the code for the field and group mappings you create with the Template Builder.
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.
Note:
For more information see Format of the XDO_METADATA Sheet and Using Advanced BI Publisher Functions.
Creating the XDO_METADATA Sheet
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.
Format of the XDO_METADATA Sheet
The XDO_METADATA sheet is created with the format shown in this figure. 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:
-
Version
-
ARU-dbdrv
-
Extractor Version
-
Template Code
-
Template Type
-
Preprocess XSLT File
-
Last Modified Date
-
Last Modified By
The Data Constraints section is used to specify the data field mappings and other processing instructions. Details are provided in the following sections.