OData reports with Project Online/Project Server (Part1)

In this blog series I’ll show you the steps how to create an OData report for your multi-project landscape in Project Online/Project Server. You get a basic idea about the security concept, the process to design reports and how to achieve an adequate performance during data refresh.

Security

If you go with OData, you’ll need to know that it is an all or nothing decision, if users can access the data feed or not. Of course you can set a password for your preconfigured report, but if the user has some basic knowledge about REST, he or she can access all the data. There’s no way to setup a fine granular security concept like it is possible on database side in an on-premise environment. Anyway, if we want to create reports in Project Online, this is the easiest way to get the data. Per default, the security groups Administrators, Portfolio Managers and Portfolio Viewers can access the data feed. If you want to setup an additional security group, you have to mark the global permission “Access Project Server Reporting Service”.

accessreportingservice

Data feed

If you enter the data feed url http://yoururl/pwaname/_api/projectdata in your browser, you can access the following tables:

projectdatatablesjpg

On one hand, there are all the enterprise custom fields timephased and non-timephased available. On the other hand, we’ll get the major SharePoint columns from the default ProjectSite (like risks and problems).

If you want to get the fields in another language, extend your url a little bit more: http://yoururl/pwaname/_api/projectdata/[en-us] .

On this level in the browser you won’t see the fields which are included in the respective tables. In order to get a detailed field list, we can do another pretty cool change to the feed: http://yoururl/pwaname/_api/projectdata/[en-us]/$metadata .

projectdata_metadatajpg

Of course we could also explore the data with Excel. Just open the program and click on Data -> From other sources -> From OData Data Feed and enter the url from your PWA (http://yoururl/pwaname/_api/projectdata/[en-us] ). You just need to check the tables you want to analyze, create an .odc file and start pivoting.

excel odata

odata tablesodc odata

Easy?! YES!!! But please note that the entire OData feed should only be used for testing purposes. In a productive environment with a larger project landscape, the amount of data would lead to intolerable data refresh times and timeouts. Due to this reason, we have to perform a litte bit more work: Create multiple data connections AND select only the fields/timeframes that are actually needed in the respective report. In the next blog, I will provide you a step to step guide how to achieve that.

Facebooktwittergoogle_plusredditpinterestlinkedinmailby feather

Leave a Comment

Your email address will not be published. Required fields are marked *