Create an Associated Excel Template

If you use Excel to perform post-processing of data that you generate using the Report Wizard, you can facilitate this process by uploading your Excel template as a PowerSteering asset and have the data automatically sent to the template. Once in Excel you can use macros, look-ups, conditional formatting and any other Excel convention to format data into multiple worksheets.

Note: The process to create an associated Excel template changed with the Spring 2015 (v13) release. Please make sure you know what PowerSteering version you are using before you begin building your templates. If you have an Excel template that was built prior to the Spring 2015 (v13) see Update an Associated Excel Template for more information on how to update it for use with later versions of PowerSteering. If you do not need to update the format or formulas in the associated Excel template there is no action required for templates created prior to Spring 2015 (v13). They will run without incident post upgrading to the Spring 2015 (v13) release.

Note: Associated Excel templates are only compatible with Report Wizard reports. Excel templates cannot be associated with advanced reports.

Note: The following Report Wizard features are not supported when using associated Excel templates. The logic must be defined in the Excel template itself.

• Sort and indent by tree hierarchy (on Group/Sort tab)
• Summaries
• Groupings (on Layout tab)
• Cell coloring and status/work type icons (on Layout tab)
• Charts

1. Click Report Wizard

1. On the Icon Bar click the Add button
2. Click Report to expand the menu, then click ReportWizard.

2. Click Save

1. Create a new Report Wizard report.
2. Click Save.

3. Click Excel

1. Click the name of the report you just created.
2. From the context sensitive drop-down menu, click Excel.

4. Click OK

1. In the dialog box, select Open with Microsoft Excel (default).
2. Click OK.

5. Add Excel Tab

• Click the plus sign to add a new tab in to your spreadsheet.

6. Map Data

Map data from the DynamicReport tab to the new tab. On this mapped tab you can use macros, look-up

Note: Data mapping will always start with the information in Column A, Row 2.

Note: Ensure math functions are enabled for the Excel spreadsheet.

6.1 Enter Tab Name (Spring 2015 v13 or later versions)

1. Erase ALL report data from the DynamicReport tab so this page is completely blank.
2. Rename the DynamicReport tab PSData. This is case sensitive so ensure the letters P, S and D are upper case.

Note: There cannot be any additional data on this page, the PSData page must be blank.

6.2 Enter Token (Winter 2015 v12 or prior versions)

1. Erase all report data from the dynamic report tab.
2. Enter ${system.DATA} in cell A1.

Caution: You should ONLY use this format if you are using a version of PowerSteering prior to Winter 2015 (v12) or prior.

7. Save Spreadsheet

1. Click File in the ribbon menu.
2. Click Save As.
3. Click Computer.
4. Under computer, select a location where you want to save your file or click the Browse button to specify a location.
5. In the Save As dialog box, click Save.

8. Click Manage Reports

1. On the Icon Bar click the Review button
2. Click Reports to expand the menu, then click Manage Reports.

9. Click Assets

• Click the Assets tab.

10. Click Add New

• Click Add New.

11. Click Browse

• In the Add new asset... dialog box, click Browse...

12. Open File

1.    Navigate to the folder where the Excel file was saved.
2.    Click on the name of the file.
3.    Click Open.

13. Click Upload

1. Confirm that the correct file name is displayed.
2. Click Upload.

14. Click My Reports

• Click the My Reports tab.

15. Click Report Wizard

1. Click the name of the report you recently created.
2. From the context sensitive drop-down menu, click Report Wizard.

16. Click Details & Schedule

• Click the Details & Schedule tab.

17. Click Save

1. From the Associate Excel template drop-down menu, select the name of the Excel file uploaded in Step 13.
2. Click Save.

18. Click Associated Excel Template

1. Click the name of the report you recently created.
2. From the context sensitive drop-down menu, click Associated Excel Template.

19. Click OK

1. In the dialog box, select Open with Microsoft Excel (default).
2. Click OK.

20. View Report

• View report data in the Excel template.

Note: You may need to click Enable Editing to view the correctly formatted data.