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.

ENVISION Canada Workspacesmceclip0.png

 ENVISION USA Workspaces
mceclip1.png

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

results.JPG

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

rt.JPG

4. Give the report a template name.

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

mceclip7.png

Report Types:
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. Available output formats include PDF and XLSX.

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. Available output formats include PDF and XLSX.

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.

23.GIF

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

mceclip0.png

9. Open the downloaded file in Excel.

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

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 first tab of the Excel ENVISION output should look similar to the following:

mceclip1.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.

rtrtr.JPG

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

csect.JPG

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.

23.GIF

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

mceclip2.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.

mceclip3.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