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.
ENVISION Canada Workspaces
ENVISION USA Workspaces
2. Once the report has run, open the Results page.
3. Click the gear icon for the report you just ran and choose Save Report Template.
4. Give the report a template name.
5. In the Export type list, select either Batch or Side-by-Side.
Batch Reports - Outputs are formatted reports, one per data slice. For example, if you had run a report on three areas, you would have an output file for each of the three areas.
Side-by-Side - Output is a single formatted report with each data slice side-by-side. For example, if you had run a report on three areas, the data for all of them would be combined into one single file, with the data for each area beside the next.
Note: Not all reports support the Side-by-Side option. If you are trying to download a number of different reports at once and Side-by-Side is not an option, please run the reports individually.
6. Click Create.
7. In the side panel, click on My Data and then Report Templates.
8. For the report template you created, click the Excel icon in the download column.
9. Open the downloaded file in Excel.
Note: If the Excel sheet shows all zeroes in any of the ‘formula’ worksheets, it is in Protected View. Click Enable Editing at the top of the Excel worksheet to continue.
10. Make all your changes in Excel from the data on the ‘Trade Area/Customers/Target Group_formula’ worksheet. Do not use either of the 'Details' pages.
Note: Do not change the positions of the first two worksheets, referenced by numbers one and two in the screen capture below. It is important that these worksheets ('Trade Area/Customers/Target Group' and 'Report Details') remain in the position in which they were created in order for the template to operate correctly.
The first tab of the Excel ENVISION output should look similar to the following:
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 'Trade Area/Customers/Target Group_formula' 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.
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.
Data can be moved into a second sheet to create a chart. For example:
11. When you are finished making changes, save the file as an .XLTX (Excel Template) file.
12. Back in ENVISION, click My Data and then Report Templates.
13. To upload the template that you created, click Upload.
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.
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.