SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

A common requirement for clients is to have a report that highlights key information about each project. This usually includes any key project level fields, up and coming tasks / milestones and any active issues or risks. For previous versions of Project Server, in my opinion, the best reporting technology for this was SQL Server Reporting Services (SSRS). For Project Server 2013 on-premise installations SSRS is probably still the best option for most reports due to the flexibility around formatting, layout etc. For Project Online, unfortunately SSRS is not an option, a nice option for a project highlight type report is Power View. This blog post will walkthrough creating a project highlight report using OData and Power View for Project Online. This example report contains key project level data, key milestones due to complete this month, future key milestones, active issues and active risks. The OData feeds used are:

https://<serverurl>/pwa/_api/ProjectData/Projects()?$filter=ProjectType ne 7&$select=ProjectId,ProjectName,ProjectOwnerName,ProjectStartDate,ProjectFinishDate,ProjectWork,ProjectCost,TotalCost,ProjectQuality,ProjectSchedule,ProjectCost,ProjectResource
https://<serverurl>/pwa/_api/ProjectData/Tasks()?$filter=TaskFinishDate gt datetime’2013-08-01T00:00:00′ and TaskFinishDate lt datetime’2013-09-01T00:00:00′ and KeyMS eq ‘Yes’&$select=ProjectId,TaskIndex,TaskName,TaskStartDate,TaskFinishDate
https://<serverurl>/pwa/_api/ProjectData/Tasks()?$filter=TaskFinishDate gt datetime’2013-09-01T00:00:00′ and KeyMS eq ‘Yes’&$select=ProjectId,TaskIndex,TaskName,TaskStartDate,TaskFinishDate
https://<serverurl>/pwa/_api/ProjectData/Issues()?$filter=Status eq ‘(1) Active’&$select=ProjectId,IssueId,Title,Discussion,Resolution,Owner,DueDate
https://<serverurl>/pwa/_api/ProjectData/Risks()?$filter=Status eq ‘(1) Active’&$select=ProjectId,RiskId,Title,MitigationPlan,ContingencyPlan,Owner,DueDate

These feeds need to be added to Excel 2013 using the “From OData Data Feed” option found on the DATA tab:

Paste the feed URL into the Link or File field:

Click Next

Select the Projects table:

Click Next

Click Finish

Select Only Create Connection and Click OK

Repeat these steps to add the other OData feeds using the same steps.

Once all of the feeds have been added, the table relationships need to be created. On the DATA tab in Excel click Relationships. This can be found in the Data Tools group.

Click New

Set up the relationships required, for this example the relationships are:

Click Close

On the INSERT tab click Power View

Design the report as required, in this example we are using Project Name as a slicer to filter the data on the other tables, this can be seen below:

Once the report layout meets your requirements, save this to Project Online. The report can then be accessed and refreshed in the browser:

Clicking a different project on the slicer will filter the data:

Enjoy