I’ve been leveraging this report in my demos for a couple of years now – basically an overview of all of the major milestones in the project portfolio color coded by schedule status. Figured I’d test my newfound OData skills and see how hard it would be to recreate in the new world of Project Server in the cloud.
Turns out not hard at all, with perhaps a minor caveat that we’ll need to modify the data model in PowerPivot. If you’re looking at recreating this report in an on-premise instance, feel free to check out the instructions in my previous post. This post is only on how to recreate the report using a cloud based instance and OData feeds.
To prepare for this report, we’ll need to create a custom field called “Major Milestone.” This will be a task text field tied to a look up table.
Next, add the field to your project templates and map the project milestones to specific stage gates in the lookup table.
The following URL will now generate the required data to generate the report.
http://demo/PWA//_api/ProjectData/Tasks()?$filter=MajorMilestone ne null&$select=ProjectId,ProjectName,MajorMilestone,TaskFinishDate,TaskFinishVariance,TaskPercentCompleted
Open the Other Connections option in the Excel Data tab, and paste the link into the OData feed option.
The only minor issue is that when you directly use the OData feed to generate the PivotTable, you can’t get the dates to render in the PivotTable properly. As far as I can tell, when you select the “max” option in the PivotTable to display the date, you get the following error message: “We can’t summarize this field with Max because it’s not a support calculation for Date data types.”
Two solutions for this as far as I can tell:
1) Insert the data into a table, and then summarize the table into a Pivot Table – at which point, the date field rolls up just fine.
2) Go into the PowerPivot data model, and set the field in question to be a decimal number.
Insert the PivotTable using the PowerPivot table as a source, and you’re back in business.
Drop that into Excel and format per these instructions to get the report….