Using XLS template for MultiPage Workbook in Excel

We can create XLS template rather than RTF template. This is very useful if we are trying to develop BI publisher reports with huge number of columns.

Limitations of Excel Templates

The following are limitations of Excel templates:

1. For reports that split the data into multiple sheets, images are not supported. If the template sheet includes images, when the data is split into multiple sheets, the images will show only on the first sheet.
2. There is no tool to facilitate the markup of the template with BI Publisher tags; all tags must be manually coded. Some features require the use of XSL and XSL Transformation (XSLT) specifications

Prerequisites

Following are prerequisites for designing Excel templates:

• Microsoft Excel 2003 or later. The template file must be saved as Excel 97-2003 Workbook binary format (*.xls).
• To use some of the advanced features, the report designer will need knowledge of XSL and XSLT.
• The report data model has been created.

To code this design as a template, mark up the cells with the XDO_ defined names to map them to data elements. The cells must be named according to the following format:

• Data elements: XDO_?element_name?
  where
  XDO_ is the required prefix and
  ?element_name? is either:
  • the XML tag name from your data delimited by “?”
  • a unique name that you will use to map a derived value to the cell
  For example: XDO_?EMPLOYEE_ID?
• Data groups: XDO_GROUP_?group_name?
  where
  XDO_GROUP_ is the required prefix and
  • ?group_name? is the XML tag name for the parent element in your XML data delimited by “?”.
  • a unique name that you will use to define a derived grouping logic
  For example: XDO_GROUP_?DEPT?
  Note that the question mark delimiter, the group_name, and the element_name are case sensitive.

Applying a Defined Name to a Cell

1. Click the cell in the Excel worksheet.
2. Click the Name box at the left end of the formula bar. The default name will display in the Name box. By default, all cells are named according to position, for example: A8.
3. In the Name box, enter the name using the XDO_ prefix and the tag name from your data. For example: XDO_?EMP_NAME?
4. Press Enter.
   The following figure shows the defined name for the Employee Name field entered in the Name box:

6. For the total salary field, a calculation will be mapped to that cell. For now, name that cell XDO_?TOTAL_SALARY?. The calculation will be added later.

After you have entered all the fields, you can review the names and make any corrections or edits using the Name Manager feature of Excel. Access the Name Manager from the Formulas tab in Excel as shown:

After you have named all the cells for this example, the Name Manager dialog will appear as shown:

Understanding Groups

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:

• For each occurrence of the <EMPS> element, , the employee’s data (name, e-mail, telephone, salary) will display in the worksheet.
• For each occurrence of the <DEPT> element, the department name and the list of employees belonging to that department will display.

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, add grouping tags around the cells that are to repeat for each grouping element.

Note that your data must be structured according to the groups you want to create in your template. The structure of the data for this example

<DATA> 
   <DEPT>
      <EMPS>

establishes the grouping desired for the report.

To Create Groups in the Template

1. Highlight the cells that make up the group. In this example the cells are A8 – E8.
2. Click the Name box at the left end of the formula bar and enter the name using the XDO_GROUP_ prefix and the tag name for the group from your data. For example: XDO_GROUP_?EMPS?
3. Press Enter.

The following figure shows the XDO_GROUP_ defined named entered for the Employees group. Note that just the row of employee data is highlighted. Do not highlight the headers. Note also that the total cell XDO_?TOTAL_SALARY? is not highlighted.

Step 6: Test the template

If you have installed the Template Builder for Excel, the BI Publisher tab will appear on the ribbon menu as shown in the following figure:

To preview your report using sample data:

1. Click Sample XML. You will be prompted to select the sample data file.
2. Click Preview.
   The sample data will be applied to your template and the output document will be opened in a new workbook. The following figure shows the preview of the template with the sample data: