Just a quick post to highlight a limitation / issue with Excel Pivot Tables that I hadn’t seen before, details below.
This particular Pivot Table uses a Data Model that contains two OData feeds from Project Server 2013, one for Project details and one for Task details. The connections can been seen below:
The relationship has been set up as follows:
I have added Programme, Project Name, Task Name and Task Start to the Pivot Table. Programme and Project Name are from the Projects table and Task Name and Task Start are from the Tasks table. The Project Name is filtered to just one project:
Looking at the Pivot Table, it looks the CPS Test project has many tasks but in fact this project only has 4 tasks:
To resolve the issue you have to add a numerical field from the Tasks table to correct the aggregation: