Using Excel Report Templates

You can create report templates in Microsoft Excel and use them to export, analyze, and share Vault reports. You can format Excel templates using all of Excel’s functionality, including pivot tables, macros, formulas, charts, and conditional formatting. Once you create a template and upload it to a report in your Vault, other users can use the template to export the report to Excel.

About Excel Report Templates

Vault generates a sample Excel template for every report. The sample includes an Export tab with all of the columns in the report and a Cover Page tab with metadata about your report, like the report name, description, and the date that it was exported. Vault generates the Cover Page tab using tokens similar to those used on a PDF report cover page.

Vault updates a report’s sample template each time you make changes to the report. You can download the sample template and customize it however you like by moving data between tabs, adding custom tabs, renaming columns, and more. However, you should not change the tokens in the sample template. Changing the tokens will prevent Vault from correctly populating the report template. If you accidentally make unwanted changes to the sample template, you can always download it again.

Downloading a Sample Template

You can find the sample template for any report in the Advanced Options section of the report. To download a sample template:

  1. Open the report and click Edit.
  2. Expand the Advanced Options section.
  3. Click Sample to download the file.

Updating a Template

You can update the sample template in several ways.

Using Column Tokens

You can use column tokens to reference data in your Excel template the same way you use tokens in Vault. Column tokens are similar to VQL syntax.

When using column tokens, be sure to keep them on the same row. Vault doesn’t recognize column tokens on multiple rows.

Using Named Ranges

Named ranges reference the label and top and bottom values of a column using these formats:

  • object.fieldLabel
  • object.fieldTop
  • object.fieldBottom

For example, in a workflow report with a column called Task Due Date, you would use the following named range in your template:

  • ${task.task_due_date__vLabel}
  • ${task.task_due_date__vTop}
  • ${task.task_due_date__vBottom}

You can use named ranges to create formulas in your template. For example, if you have an expense report template with a Total column, you could calculate the sum of the Total column with the following formula:

=SUM(expense__v.total__vTop:expense__v.total__vBottom)

When you add a formula to a column in a template, the formula is automatically copied down to all of the cells in the column.

Uploading a New Template

Once you have customized your Excel template, you can upload it back to Vault:

  1. Open the report and and click Edit.
  2. Expand the Advanced Options section.
  3. Click Upload and select the template file on your computer.
  4. Save the report.

Modifying an Existing Template

To make updates to an existing Excel template:

  1. Open the report and click Edit.
  2. Expand the Advanced Options section.
  3. Open the existing template’s Actions menu and click Download.
  4. Make changes to the template file in Excel.
  5. Open the template’s Actions menu again and click Upload to load the updated template.

Exporting to a Template

Once you have created an Excel template and uploaded to a report in Vault, you can export the report to the Excel template. Vault populates the template with data from the report and saves it as an Excel file with the same file name as the template.

How to Export a Report to an Excel Template

You must run the report before you can export it to the Excel template. After you run the report, open the report’s Actions menu, click Export to Excel and select Template.

Removing a Template

To remove an Excel template from a report:

  1. Open the report and click Edit.
  2. Expand the Advanced Options section.
  3. Click Remove from the template file’s Actions menu.
  4. Save the report.

Limitations

  • Matrix reports are not compatible with Excel templates.
  • Excel templates must be XLSX or XLSM files to be used with Vault reports. Vault does not support XLS file templates.
  • Excel templates do not copy down table formatting. We recommend using conditional formatting instead.
  • You can only include tokens in the first five (5) sheets of a template.
  • Excel templates support unzipped Excel files up to 10 MB. We recommend deleting unwanted external data and empty cells and clearing the pivot table cache.

The following permissions control your ability to upload an Excel template to a report:

Security Profile

Objects: Report: Edit
Controls ability to upload a template to a report.