Get your filters to stick in PivotTable reports

Have you ever tried to make use of filters in your PivotTable reports, only to find that they don’t stick when you save your template back to the report in the Report Manager?  When your workbook is generated, maybe you’d prefer blank items to be hidden, or you would like it to default to the current year, for example.  This is all possible with an easy change to your PivotTable settings.

1.Run out your report and make the necessary changes to your PivotTable filters. This can include high level filters, field filters, label or value filters.


2. Once done, right click on your Table and select PivotTable Options.


3. Then, under the Data tab, set Number of items to retain per field to Automatic, and click OK.


4. Next, save your template back to your report in the Report Manager using the Save Excel Template button.


Now, the next time your run out your report, you’ll find that your filter settings have retained themselves. This saves you time and prevents you having to redo work when you run out your PivotTable reports.