Adding a Pivot Chart and a Pivot Table

If you want to improve your existing Resources Details List report to include a pie chart to show resources by category, and a pivot table to count resources by role and category, with filters for manager and department, refer to the example of how this report should look like in Samples\2 - Adding a Pivot Table and a Chart\1 - Target report with Charts.xlsx.

Whenever adding charts and pivot tables to an existing Excel report that lists the data, it is advised to follow these steps:

  1. Start from the report result (in this case, Samples/1 – Your First Report/6 - Created Report with SQL query.xlsx).
  2. Create one or more Dynamic Named Range containing the data needed in the charts and pivot tables (see Defining a Dynamic Named Range for detailed instructions).
  3. Create all the charts and pivot tables using the dynamic named ranges. Ensure that the option Refresh data when opening the file is selected in the pivot table options on the Data tab.
  4. Replace the business data with the template tags and placeholders. You can copy them from an existing excel template (in this case, from Samples/1 – Your First Report/5 - Template with SQL query.xlsx).
  5. Remove every template placeholder or template tag from the pivot tables cells; otherwise they will be evaluated when the report is created and will cause errors. Replace all of these with a single space; the correct data will be inserted when the report is created if you selected the option to refresh data when opening the file in Step 3.

After this is done, you should obtain a document similar to Samples\2 - Adding a Pivot Table and a Chart\2 - Template with Pivot Chart and Pivot Table.xlsx.

Copy this template on your PPM Server, run the report. The result should look like Samples\2 - Adding a Pivot Table and a Chart\3 – Created Report with Pivot Chart and Pivot Table.xlsx.

Note: If you download the PPM Report in Internet Explorer and use the “Open” menu instead of “Save”, the report file will be opened in read-only mode directly from the PPM Server. It won’t be saved locally, and this will cause pivot datasources not to load correctly when opening the file in Excel 2013 and later. If your report has dynamic pivot tables, please use the “Save” or “Save as” menu when opening PPM Excel Reports in Internet Explorer.