Creating Excel Templates to Update Project Matrices

The procedure below describes how to add a matrix to an Excel template that displays and updates values when published to Accolade. A single template can also contain metrics, project metadata, and metrics in related projects.

Important! If using the calculated metric expression CurrentMatrixValue in a matrix and updating the matrix in Excel, you must uniquely identify other matrix rows by flagging metrics as unique. If matrix columns are not unique or the Excel workbook contains duplicate columns, the Smart Excel template does not update, upload, or publish back to Accolade.

To set up an Excel template to update a project matrix:

Note:  The following procedure assumes that you have created the matrix within Accolade, added its metrics, and associated it with the appropriate models. The following procedure also assumes you are familiar with basic Excel functionality.

1. Open the Process Document - Smart Excel <version>.xltx file and save a copy to your computer, using a file name that clearly identifies the purpose of the template.

Ensure that the master Process Document - Smart Excel <version>.xltx file is kept in a safe place, such as the template library, with no modifications to it.

2. (Optional) Hide the SGM_MetricTransfer, SGM_Metadata, SGM_RelatedProjectMetrics, SGM_DeliverablesActivities, and SGM_Notes worksheets, as none are required for a matrix update; however, do not delete them.
3. Add a new worksheet and name it SGM_MX_<matrix system name>.

This sheet is the matrix sheet. The matrix system name must match the system name assigned to the matrix in Accolade exactly. Add additional sheets for each matrix, as necessary.

4. Save the file, add it to the Template Library as a Process Document type, and add the template to a deliverable or activity in a model that has the matrices associated to it.
5. Open a test project that uses the matrices for which you created worksheets for in step 3, navigate to the deliverable or activity it is assigned to, and download the template.

Downloading the template from a project populates the matrix worksheet with two copies of the matrix.

6. On Sheet1, create the display worksheet that users of the template interact with, identifying and noting the purpose of the cells throughout the layout. More...

   • Cells that only display downloaded data from Accolade.
   • Cells that only upload changed values to Accolade.
   • Cells that contain information and data that is not sent to or received from Accolade. For example, label cells.

Enter all the labels, column headings, background color, formulas, and so on, into the cells that do not receive or send data to Accolade to verify that the worksheet looks as you intend it to for users.

It can be useful as you are laying out the display worksheet to name the upload and download cells to more clearly identify them in the layout prior to entering the field codes to establish the data connection with Accolade.

You can add additional display worksheets, as necessary.

7. (Optional) Rename Sheet1 to an appropriate, meaningful name for the display worksheet for users completing the template.
8. Create cell references between the display worksheet (originally Sheet1) and the matrix worksheet(s) created in step 3, adding references to only cells in the right copy of the matrix where values are loaded to Accolade. More...

    

   Cell Purpose on Display Worksheet	Instructions
   Cells that only display downloaded data from Accolade	On the display worksheet, in the cells that display the current metric value when the document is downloaded and opened, create cell references to the corresponding "current value" cells on the left side of appropriate matrix worksheet.
   Cells that document owners change to upload back to Accolade	On each matrix worksheet, in the "new value" cells in right side of the worksheet, enter cell references to the cells on the display worksheet where the document owner enters new values to upload back to Accolade. Only add cell references to cells in the right copy of the matrix where values are uploaded to Accolade. Cell references or formulas added below the cell references that make up the table in the right copy cause NULL rows or invalid data to be added to Accolade.

9. Protect the display worksheet.

Protecting the display worksheet keeps users from overwriting cells that contain references or formulas.

Important! Do not protect the entire workbook. The SGM_ worksheets must remain unprotected for the download and upload between Accolade to work correctly.

10. In the display worksheet, remove the lock on the cells that users use to update metric values.
11. Hide the SGM_MX_<matrix system name> worksheet created in step 3, as this is a worksheet that users interacting with the template do not need to see.

Prior to saving, select the cell in the display worksheet where a template user typically starts typing in the template, to default the cursor to that cell when the template is opened.

12. Save the file and upload the new version to the template to the Template Library.

Matrix Sheet Layout

When you open the template from a test project, the matrix sheet is populated with two copies of the matrix that manage the upload and download in the worksheet that matches the matrix system name. The copy on the left contains "current" values that are downloaded to the worksheet from Accolade. The copy on the right contains "new" values to update to Accolade. The metric names are prefixed with New in the headers in the copy on the right.

Each copy contains the following:

Column	Description
Row ID	The leftmost column of each matrix copy. The other columns contain the metrics. The workbook does not use the numbers in the RowID column to identify the rows. Rows are identified by their position below the headings in the sixth row. You can enter whatever row ID numbers or names are most useful to you to orient yourself on the sheet.
Metrics	The metrics contained in the matrix. Each metric contains the following information. Row 1 - Display names of relative date metrics. Row 2 - Periods (month, quarter, or year) of relative date metrics. Row 3 - Current relative to dates of relative date metrics. Row 4 - Indicates whether a metric is set to be unique within the matrix. "Y" or "N" for all metrics. The template does not enforce the uniqueness of a metric. The indicator is only a reminder of which columns, or combinations of columns, should contain unique values. Row 5 - List items for list metrics. The cell contains all of the list values for the list metric in the column, including cascading (filtered) lists. Row 6 - The system names for each metric. Row 7 - Column headings are the display names for each metric. Row 8 and below - Metric values from the display sheet in the left copy of the matrix. In the right copy, cell references to the display worksheet.

 

Important! Do not edit or delete the "current" values in the copy on the left. Removing these values will cause errors with your worksheet upload.

Notes:    Sopheon recommends that all SGM_ worksheets should be hidden prior to publishing for use in a process model, to prevent users from making inadvertent changes. Do not rename these worksheets, and do not delete them from the file. The Process Document - Smart Excel <version>.xltx Smart Excel template contains a necessary custom document property and five worksheets that are not used for matrices. If you use an ordinary Excel workbook rather than the Smart Excel template, you must add the custom document property SGM_SmartExcelVersion, whose value is the Accolade version number. The Database Reference Tables Command Timeout system parameter governs the timeout, in seconds, for uploading each matrix from Excel to Accolade.