Skip to main content

Last updated: 2025-09-19


Analytic (Excel) Reports - Creating Excel Templates


Overview


Users who know how to create Excel templates can create their own analytic report templates. Advanced Excel users can add formulas, PivotTables and coding to the template.


This section describes how to:


  • Create a custom Excel templates for use with analytic reports.
  • Add the template to SmartOffice.
  • Link the template to a Dynamic Report.

Creating an Excel Template


Note: Excel templates created using earlier versions of Excel may not function properly in later Excel versions.


From Excel


  1. Open a new workbook in Excel.
  2. In the first row, enter the appropriate column headings. The headings and their order must exactly match those in the Dynamic Report that the template will be attached to.
  3. If appropriate, advanced users can add worksheets, formulas and other Excel features.
  4. Save the file to the local computer as an .XLT or .XLTM file.

From a Dynamic Report


  1. If the Dynamic Report that will be associated with the Excel template does not already exist, create the Dynamic Report.
  2. Run the Dynamic Report and export the results to an Excel file as described in Running an Analytic Report from the Dynamic Report List.
  3. In Excel, delete all data except for the column headings.
  4. If appropriate, advanced users can add worksheets, formulas and other Excel features.
  5. Save the file to the local computer as an .XLT or .XLTM file.

Adding an Excel Template to SmartOffice


  1. From the SmartOffice side menu, select Setup > Excel Templates to open the Search Documents dialog box.
Image from base_dialog_search_documents.png
  1. Click the Search button to display the Excel Template List.
Image from base_list_excel_templates_generic.png
  1. Select Menu > New 'Documents' Record.
  2. If your browser asks for permission to open the SOProLauncher app, select Open SOProLauncher. If prompted to download an .sopro configuration file, save and open the file.
  3. If prompted, sign in to the Microsoft Plug-in for SmartOffice (this normally happens automatically).
  4. When the Windows file browser dialog box opens, select the .XLT or .XLTM file.

Linking an Excel Template to a Dynamic Report


The instructions in this section assume that the user is familiar with creating and modifying Dynamic Reports. For help with these topics, see Creating a Dynamic Report and Modifying and Copying Reports.


  1. Create a Dynamic Report or open an existing report for modification.
  2. In the List Layout step of the Dynamic Report Setup wizard, ensure that the report's column headings and column order match those in the Excel template.
  3. In the Details/Add Filter step of the wizard, click the Excel Templates hyperlink to open the Search Documents dialog box.
Image from base_dialog_search_documents.png
  1. Enter a partial or full description and/or keyword for the Excel template.
  2. Click the Search button to display a list of Excel templates that match the search criteria.
  3. Click the name of the appropriate Excel template to select it.
  4. Make any other necessary additions or changes to the report setup.
  5. Click the Finish button.

After linking the template to the report, the user can run the analytic report.