Now we have an Excel workbook with the appropriate data connections to Project Server….
Let’s break out PowerPivot to modify the data model. Click on the Manage button in the PowerPivot tab.
You should see something that looks like this:
Rename the tabs to something a bit more useful.
Now let’s add a custom field to the Forecast and Baseline tables. We’ll use this field to map the data back to the TimeSet table.
I used this formula for the Forecast table:
..and name the new field “Total Date.”
I repeat the process for the Baseline data set, but use the TaskBaselineFinishDate value to generate my Total Date field.
Now let’s tie it all together. In the Home tab, select the Diagram view.
Add the following joins to the data model.
If I select the option to Manage Relationships in the Design tab, I should see the following:
At this point, we simply need to add a pivot table to Excel and point it at our data model.
Our data model appears as a table in the External Connections list.
…from there, you need to add the appropriate fields to the pivot table.
Note that I added each of the date fields twice. I’m going to flip the second instance of those fields to display a running total and give them a more user friendly name…
From here, it’s a matter of following these instructions to create a chart.
Not entirely sure the juice was worth the squeeze on this particular chart, but still a worthwhile exercise. Add the slicer and timeline control for extra cred as an Excel 2013 master.