In our last tip, you discovered that with an existing report, you’re able to make and save changes to a layout as a result of the Excel template that is associated with it. This means that any modifications you make persist each time the report is run, with the latest data being populated. If you’d like to catch up on the tip, you can find it here.
If you create a new report, there is initially no template included with it. In this tip, you’re going to learn how to add one.
To start with, I have set up a new Sales report in the Report Manager. You can see by looking at the Report Template field under its properties that there is no template associated with it.
To include one, follow these simple steps:
1. Run out your report. This will output your raw data into a new workbook and you can create your layout(s) from it.
2. Now, with the workbook left open, go back to the Report Manager, select the report, and click Save Excel Template on the Home tab.
3. A list of the open workbooks is then displayed, select the one that you want saved and click OK.
4. If you have the option, Parameters on Second Worksheet, selected under the reports properties, you’ll be asked whether it’s okay to clear the sheet. Unless you have placed content on the second sheet, this is fine, so click Yes. Otherwise clicking No will cancel the process.
5. You’ll then be asked to select the format you’d like the template saved as. It is recommended that you choose .xltx if you’re working with a standard workbook, or .xltm if your workbook includes macros.
6. Once you click OK, you’ll be asked to give your template a name. I’m going to leave mine as the name I gave my report.
7. If you have used PivotTables in your layout and they have the ‘Save source data with file’ option selected, it will be suggested that this be switched off. If you don’t need it, then click Yes. Otherwise you can select No.
8. You may then receive a message saying that the workbook contains external data and that Microsoft can clear it before saving the file. It’s recommended that you select Yes here.
9. The workbook is then saved with the given name and extension to your templates storage location and linked to the report. You’ll receive a message confirming that the process has completed successfully. You’ll also be able to see the file included in the Report Template field.
Now, each time you run out your report, the template you have saved will be used for it, populated with the latest data. Also, if you would like to make any further changes to your layout(s), then you can do so by following the steps in the previous tip.
