Use 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 Publisher from your Excel session.

The Publisher tab that displays when you install the Template Builder is shown in the following illustration.

Description of the illustration exc_tb_menu.gif

You can use the Template Builder in connected mode or disconnected mode. In connected mode, log in to the Publisher server from Excel. The connection enables you to browse the 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 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:

Work in Connected Mode

In connected mode you can interact directly with Publisher.

The process flow for creating or editing a template in connected mode is:

Open Excel with the Template Builder for Excel Add-in installed.
Log on to Publisher .
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 catalog.

Log In Through the Template Builder

The Excel Template Builder enables a direct connection to Publisher from your desktop Excel session.

By logging in directly to 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 in to Publisher from Excel:

In Excel, on the Publisher tab in the Online group, click Log On.
In the Login dialog, enter your Publisher username, password, and the URL. Publisher URL format: http://www.<host>:<port>/xmlpserver.

Online Features of the Template Builder

After logging in, the following commands in the Online group become enabled.

Description of the illustration exc_op_temp_icons.gif

Log Off - ends the connection to Publisher.
Open - enables interaction with the Publisher catalog.
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 Publisher catalog.

Access the Publisher Catalog from the Template Builder

The Open online command enables interaction with the Publisher catalog.

The Open command launches the Open Template dialog to enable access to the Publisher catalog.

Description of exc_op_temp_diag.gif follows

Description of the illustration exc_op_temp_diag.gif

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.

Upload Templates from the Template Builder

A link to upload templates is provided if you are online with the server.

If you've maintained the connection during the design process, click one of the following to upload your completed template to the Publisher server:

Upload Template uploads your edited template and replaces the existing template in the catalog. Upload Template is enabled only when you've 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 Publisher catalog.

Work in Disconnected Mode

When direct connection to Publisher isn't possible or practical, 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 Publisher and download sample data from the data model for which you want to design a template.
Open Excel with the 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 Publisher and use the report editor to upload your template.

Obtain Sample Data

The Template Builder requires sample data to insert the data field mappings to your template.

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 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.

Load Sample Data in Disconnected Mode

You can load sample data into a local directory while offline.

Once you've saved the sample data from the report data model to a local directory, load it to the Template Builder.

Open Excel with the PublisherTemplate Builder for Excel Add-in installed.
On the Publisher tab, in the Load Data group, click Sample XML. You're prompted to locate and select the data from its saved location. A confirmation message confirms the data is loaded.

Upload 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 Add a Layout by Uploading a Template File.

Insert Fields

The Field command in the Insert group maps data elements from the loaded sample data to the desired location in the spreadsheet.

The maximum number of fields you can add using Template Builder to an Excel template is 990.

In Excel, select the cell to which to map the data element.
On the Publisher tab, in the Insert group, click Field. The Field dialog launches, displaying the data elements from your sample data.
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.

Description of the illustration exc_temp_ins_field.gif
Click Insert to insert the data element to the cell in the spreadsheet. Sample data is inserted to the cell.

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.

Description of the illustration excel_conept.gif

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 isn't available if sample data has been loaded from a locally stored file or when the data model doesn't include Business View names.

Insert Repeating Groups

You can insert repeating groups of cell elements.

To insert a repeating group:

Select the cells in the spreadsheet that contain the elements you want repeated.
On the Publisher menu, in the Insert group, click Repeating Group.
Enter the appropriate fields in the Publisher Properties dialog.
When you've completed the dialog options, click OK to insert the Publisher code to define the groupings. An Excel defined name is assigned to the cell range using the Publisher syntax XDO_GROUP_?name? and the code is written to the XDO_METADATA sheet as shown in the following illustration.

Description of the illustration exc_tb_rg_meta.gif

Use 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:

On the Publisher menu, in the Tools group, click Field Browser.
The Field Browser displays the 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.

Description of the illustration exc_tb_field_b.gif
To delete the code, click Delete. To edit the code, update the code displayed in the Edit and click Update.
When finished, click Close to close the Field Browser.

Preview Templates

Use the preview feature of the Template Builder to test your template before uploading it to Publisher.

To preview a template with the loaded sample data:

On the 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're working in connected mode, you can test your template with live data from the report data model using View Report.

To view your template using live data:

On the Publisher tab in the Online group, click View Report.

The Template Builder sends a request to execute the data model in Publisher and returns the data to apply to the template. If the data model requires parameters, you're prompted to enter values. The output document is opened in a new Excel workbook.

Import Excel Analyzer Templates

The Excel Analyzer feature of Publisher has been deprecated, but if you have Excel Analyzer templates from previous 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:

Open the Excel Analyzer template. If you're 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.