When you add a new report definition with Excel selected in the Function menu, you can attach an Excel file that renders the data using tools like pivot tables or charts when your users generate a report using your report definition.

Any users creating excel templates for report definitions with advanced Excel features should be competent Excel users. The example below is not intended to provided detailed step-by-step instructions.

Example: Create an Excel Template That Uses a Pivot Table

To set up an Excel template, begin by exporting an existing Excel output for the product and data sources (objects) for which you are creating the report definition.

Excel File with Data on First Worksheet

When you've exported the data, add a second worksheet to the Excel file. In the new worksheet you create, insert a pivot table.

Inserting a Pivot Table in the Second Worksheet

Return to the first worksheet that has the data you exported from VertiGIS FM and select the range of cells you want included in the pivot table.

Selecting the Pivot Table Range in the Dataset

After you click OK, you can drag fields from the data sheet into their desired locations in the pivot table.

Pivot Table Fields from the Dataset

The resulting pivot table should resemble the screen capture below.

Pivot Table with Columns and Data from the Exported Dataset

Depending on the type of data exported and how you want it presented in the pivot table, you may need to right-click in the pivot table and select a different option from the Summarize Values By menu.

Summarize Values By Menu in Excel

You can also format the cells in the table (right click > Format Cells).

Format Cells Dialog in Excel

To ensure the pivot table populates with current data as soon as a user opens it when they export a dataset from VertiGIS FM, ensure the Refresh data when opening the file check box is selected. You can access it by right-clicking in the pivot table and selecting PivotTable Options and then opening the Data tab.

Data Tab in the PivotTable Options

You can then delete all the data from the original worksheet that contains the dataset and save the Excel document.

You can upload the Excel file in the Excel Template section by clicking Place files for upload here (or click) and double-clicking the Excel file in Windows Explorer. Alternatively, you can drag the file from a Windows Explorer window into the box.

Link to Upload an Excel Template File