The Excel Template Builder facilitates template design by automating the insertion of simple mappings, providing preview functionality, and enabling direct connection to the Publisher server from your Excel session.
The Publisher tab that displays when you install the Template Builder is shown in the following illustration.
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 the Publisher server.
The process flow for creating or editing a template in connected mode is:
- Open Excel with the Publisher Template Builder for Excel Add-in installed.
- Log on to the Publisher server.
- 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 Publisher 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:
Online Features of the Template Builder
After logging in, the following commands in the Online group become enabled.
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.
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 the Publisher server 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 the Publisher server 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 the Publisher server 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 Publisher 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.
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:
- 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.
- 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.
Note that the XDO_METADATA sheet is hidden by default.
More Features of the Field Dialog
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 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.
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.
- 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.
Use the preview feature of the Template Builder to test your template before uploading it to the Publisher server.
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 on the Publisher server 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.
- 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.