Setting Custom Excel Report Template Properties

Using the Accolade Office Extensions add-in to create Excel reports, you can add and modify custom properties based on your report and template needs. Use the custom Excel document properties described below for Excel reports attached to a deliverable or activity or available from the Charts & Reports page. Custom properties include how data is refreshed, server information, and where log files are saved.

Note:  Accolade does not refresh the data for non-document owners, regardless of the settings in the file.

Refresh on Open

Microsoft templates contain a custom property, SGM_SYS_RefreshOnOpen, which determines how the document's content is refreshed when it is opened in Accolade.

If the report is used as a template in a deliverable or activity, when a document owner downloads the template from the Deliverable or Activity dialog within a project, the data is refreshed, regardless of the SGM_SYS_RefreshOnOpen setting. For document versions, document owners are prompted to refresh the data.

To set the refresh on open properties in an Excel report template:

1. Create the Excel report template and save it to Accolade.
2. Download the document and open it in Excel.

Downloading the document from Accolade saves Accolade-specific properties to the document.

3. Display the document's advanced properties.

How to display the advanced properties varies across applications and versions. Typically, property options are located under the File menu. Refer to the Excel online Help for the specific location in the version you are using.

4. In the Properties dialog box, click the Custom tab.
5. From the properties list, select SGM_SYS_RefreshOnOpen.
6. In the Value field, enter one of the following options:

• ALWAYS - Refreshes the template automatically on open from Charts & Reports.
• PROMPT - Displays a prompt to select whether to refresh the data.

7. Save the template and add it to the Template Library.

Refresh Fields on Worksheets

Add the custom SGM_RefreshFieldsOnWorksheet property to an Excel report template to specify which worksheets are scanned for Accolade field codes. Specifying worksheets using this parameter can improve the performance of large reports.

To set which worksheets to scan for field code refresh:

1. Create the Excel report template and save it to Accolade.
2. Download the document and open it in Excel.

Downloading the document from Accolade saves Accolade-specific properties to the document.

3. Display the document's advanced properties.

How to display the advanced properties varies across applications and versions. Typically, property options are located under the File menu. Refer to the Excel online Help for the specific location in the version you are using.

4. In the Properties dialog box, click the Custom tab.
5. In the Name field, enter the case sensitive name SGM_RefreshFieldsOnWorksheet.
6. From the Type list, select Text.
7. In the Value field, enter the names of the worksheets that contain Accolade field codes, separating the names with a backslash (\).
8. Click Add.
9. Save the template and add it to the Template Library.

Enable Output Log Files

Add the custom SGM_LOG_DIRECTORY property to an Excel report template to create an output file that collects log files from the Accolade Office Extensions add-in. Use the generated logs file when troubleshooting server call issues.

1. Create the Excel report template and save it to Accolade.
2. Download the document and open it in Excel.

Downloading the document from Accolade saves Accolade-specific properties to the document.

3. Display the document's advanced properties.
4. In the Properties dialog box, click the Custom tab.
5. In the Name field, enter the case sensitive name SGM_LOG_DIRECTORY.
6. From the Type list, select Text.
7. In the Value field, enter a file folder path where the logs will collect.
8. Click Add.
9. Save the template and add it to the Template Library. Save the workbook with the added custom property to enable the add-in to collect logs to the specified directly.

To disable the add-in from collecting logs, remove the custom property and save the workbook.