Using PivotTables To Create Report Templates

 When using a PivotTable in the template file take the following into account:

1. PivotTable Limitations – ensure that you do not display too many row fields that could cause the pivot table limitation to be reached. Use report filter/page fields where necessary to further summarise data.
2. When pivoting data from a source data sheet where the data was rendered by Alchemex, always use the named range Sheetname!RawData. This will ensure that the full range of data extracted is always available for use within the pivotTable. When adding new columns to a report, these new columns of data will then automatically be included in the pivotTable range.

Figure 7: Selecting PivotTable Data Range

3. Turn off the feature “Save data with table layout”. This is a pivotTable option which affects the file size when turned on, resulting in unnecessarily large template files.
4. Use the feature “Clean Template Pivot Fields” in the Report Manager to clear out pivot table items before exporting the report for delivery. This will ensure that the user of the report does not view items within the pivot table that were used in the development of the report.

5. Suggested PivotTable settings:

• Set the following footers on each page:
  • At the bottom left, set the date and time
  • At the bottom right, set page numbering
  • Set print titles to have row headings repeated at the top of each page.
  • Set print titles to have columns repeated at the left of each page where necessary.
  • Preview the report and adjust the scaling where necessary, but to a minimum of 75%.
  • Reset margins where necessary.

6. Formatting should be uniform on all reports to ensure the end product has a consistent feel.
7. When formatting a pivot, the use of bright colours should be avoided.  It should also be taken into account that colour schemes may vary on different systems.
8. It is recommended that the “Quick Pivot” & “Format Pivot” tool be used to format all pivot tables.  This can be found under the “Add-Ins tab in excel.

Figure 8: PivotTable format menu

Figure 9: Standardizing the format of PivotTables