Using Excel Templates

Within ENVISION you can work with Excel templates to allow for greater customization of your report output. You can take an existing output and customize it in Excel, then use it as a template for running further reports.

1. Begin by running one of the following reports in ENVISION.
mceclip0.png
                      Canadian Workspaces   

 mceclip1.png
                      American Workspaces

2. Once the report has run, open the Results page. 

mceclip2.png

3. Click the gear icon mceclip3.png for the report you just ran and choose Save Report Template.

mceclip6.png

4. Give the report a template name.

5. In the Export type list, select either Batch or Side-by-Side.

mceclip7.png

6. Click Create.

7. In the side panel, click on My Data and then Report Templates.

mceclip8.png

8. For the report template you created, click the Excel icon in the download column.

mceclip9.png

9. Open the downloaded file in Excel.

Note: If the Excel sheet shows all zeroes, you may need to click Enable Editing.

mceclip10.png

10. Make all your changes in Excel from the data on the ‘formula’ worksheet that matches the report name. Do not use either of the Details pages.

The Excel ENVISION output should look similar to the following:

mceclip11.png

You can change a number of things in Excel, including:

  • The sort order
  • Moving fields
  • Reformatting fields
  • Hiding/removing fields
  • Column headings
  • Branding (e.g. colors/colours)
  • Adding charts
  • Adding new worksheets

Tip: When copying and pasting individual fields in Excel, make sure to copy the formulas. The formulas are all found on the second worksheet. For example, when moving a value from one cell to another, you may not be able to just copy and paste.  You may have to actually copy the formula (as shown below) and paste it into the cell you wish. 

mceclip12.png

Tip: If you are more experienced with Excel, you may want to consider copying and pasting the variable codes, and using a VLOOKUP to pull the descriptions and data from the first worksheet, to populate another.

This is the modified report to be uploaded back into ENVISION. Note some of the changes, including the color/colour, moving cells, formatted dollar values, and new sheets added.

mceclip15.png

Data can be moved into a second sheet to create a chart. For example:

mceclip16.png11. When you are finished making changes, save the file as a .XLTX (Excel Template) file.

12. Back in ENVISION, click My Data and then Report Templates.

mceclip18.png

13. To upload the template that you created, click Upload.

mceclip19.png

14. Once you see the status message in the bottom-right corner indicating your report has been uploaded, you can begin to use it.

Running a New Report Using a Template
1. Select the template you wish to use and click Run.

mceclip20.png

2. Select the trade area and benchmark and click Create Dashboard.

3. Navigate to the Results page and click the name of the report. It will automatically download the Excel output. The resulting report will be formatted to reflect the changes you made in the Excel template that was previously uploaded.

 

Powered by Zendesk