Build a Basic Template Using the Template Builder

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:

Step 1: Load Sample Data to the Template Builder

Loading sample data provides a properly formatted base from which to build a template.

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.

<?xml version="1.0" encoding="UTF-8"?>
<! - Generated by Oracle BI Publisher 11.1.1.4.0 - >
<DATA>
 <DEPT>
   <DEPARTMENT_ID>20</DEPARTMENT_ID>
   <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
     <EMPS>
       <EMPLOYEE_ID>201</EMPLOYEE_ID>
       <EMP_NAME>Michael Hartstein</EMP_NAME>
       <EMAIL>MHARTSTE</EMAIL>
       <PHONE_NUMBER>515.123.5555</PHONE_NUMBER>
       <HIRE_DATE>1996-02-17T00:00:00.000+00:00</HIRE_DATE>
       <SALARY>13000</SALARY>
     </EMPS>
     <EMPS>
       <EMPLOYEE_ID>202</EMPLOYEE_ID>
       <EMP_NAME>Pat Fay</EMP_NAME>
       <EMAIL>PFAY</EMAIL>
       <PHONE_NUMBER>603.123.6666</PHONE_NUMBER>
       <HIRE_DATE>1997-08-17T00:00:00.000+00:00</HIRE_DATE>
       <SALARY>6000</SALARY>
     </EMPS>
  </DEPT>
<DEPT>
...
...
</DEPT>
</DATA>

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 <Install Directory>\BI Publisher Desktop\Template Builder for Word\samples\Excel templates\Employee By Departments\EmpByDept Single Sheets\EmpbyDeptExcelData.xml

Step 2: Design the Layout in Excel

Use Excel to simplify a design layout.

In Excel, determine how you want to render the data and create a sample design, as shown in the following illustration.

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 isn't available in the data and requires a calculation.

Step 3: Use the Template Builder to Insert Fields

You can map data to data fields in the template.

In this layout design, the following fields must be mapped to the template from the data:

Field in Layout Element in Data

Department

DEPARTMENT_NAME

Employee Name

EMP_NAME

Employee ID

EMPLOYEE_ID

Email

EMAIL

Telephone

PHONE_NUMBER

Salary

SALARY

To insert field mappings using the Template Builder:

  1. Select the cell in the spreadsheet where the data field is to display. For example, to map the DEPARTMENT_NAME element, select cell B5.
  2. On the Publisher tab, in the Insert group click Field. (Because this is the first field you are inserting, a message displays stating that the metadata sheet will be created.) The Field dialog displays showing the data structure, shown in the following illustration.
  3. Select the element in the Field dialog and click Insert. Sample data is inserted to the cell in the template.
  4. Repeat for the Employee Name, Employee ID, Email, Telephone, and Salary fields in the template.

Step 4: Use the Template Builder to Insert Repeating Groups

A group is a set of data that repeats for each occurrence of a particular element.

In the sample template design, there're two groups:

  • For each occurrence of the <EMPS> element, the employee's data (name, e-mail, telephone, salary) is displayed in the worksheet.

  • For each occurrence of the <DEPT> element, the department name and the list of employees belonging to that department are displayed.

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

<DATA> 
   <DEPT>
      <EMPS>

establishes the grouping desired for the report.

To insert the repeating group for Employees:

  1. Select the cells that make up the group. In this example, the first group is the Employee data that makes up a row in the table, the cells are A8 - E8.

  2. On the Publisher tab, in the Insert group, click Repeating Group.

  3. In the Properties dialog, select the following:

    • From the For Each list, select EMPS.

    • From the Group By list, select EMPLOYEE_ID.

To insert the repeating group for Departments:

  1. To define the department group, select the Department name cell and all the employee fields beneath it (A5-E9) as shown in the following illustration.
  2. On thePublisher tab, in the Insert group, click Repeating Group. Notice that the total salary cell is included in the department group to ensure that it repeats at the department level.
  3. In the Properties dialog, select the following:
    • From the For Each list, select DEPT.

    • From the Group By list, select DEPARTMENT_ID.

Step 5: Insert the Calculated Salary Field

Finally, insert the second Salary field that is to be an aggregated sum for each department.

To insert the calculated field:

  1. Select the cell in the spreadsheet where the calculated salary is to display. In this example, the cell is E9.
  2. On the Publisher tab, in the group, click Field to display the dialog.
  3. Select the SALARY element and click Insert to insert the mapping in the template.
  4. Open the XDO_METADATA sheet.

    The Template Builder created a hidden XDO_METADATA sheet when you inserted the first field. Unhide the sheet in your workbook by right-clicking Sheet1 and selecting Unhide from the menu.

    The following illustration shows the XDO_METADATA sheet for the sample template.

    The total salary field maps to the cell named XDO_?XDOFIELD7?.

  5. In Column B enter the calculation as an XPATH function. To calculate the sum of the SALARY element for all employees in the group, enter the following: <?sum(.//SALARY)?>. The entry is shown in the following illustration.

Step 6: Test the Template

You can test a template using Preview.

To preview a template with the loaded sample data:

  • On the Publisher tab in the group, click Excel.

    The sample data is applied to the template and the output document is opened in a new workbook.