The beginner's guide for Project Server/Project Online reporting using oData

Starting with a definition: “OData is a REST-based protocol for querying and updating data and is built on standardized technologies such as HTTP, Atom/XML, and JSON. It is different from other REST-based web services in that it provides a uniform way to describe both the data and the data model.”

Source: webopedia.com

Applied to Project Server (2013 +) or Project Online, ProjectData is the OData service, implemented with the OData V3 libraries, which allow to query project data.

You can access the ProjectData service through a Project Web App URL. The XML structure of the EDM is available from the http://<pwa_site>/_api/ProjectData/$metadata.

Entities are:

Limits for ProjectData queries

There is a limit to the number of entities that can be returned in one query of the ProjectData service.

Extracting some data from my Project Server instance

You can use many tools to query an oData service and get the results; the easiest way is to use Excel (2013 or later).

Let’s query the list of projects of my PWA instance.

In Excel, on the Data tab, click on From Other Sources > From oDada Data Feed.

The first screen of the Data connection wizard, allow you to input the location of the data feed (or the query) and your log on credentials info.

Next, you have to select tables you want to export (based on the previous query), in this case only Projects is available.

You can click on Finish, or Next > to save your data connection for future use.

Finally, click on Finish.

You will then be prompted to select how data will be inserted into your workbook. Default (Table) will paste data into your workbook (location defined below) as a table. You can also decide to only create connection, data feed will be added to the Excel data model for future use (very useful to create complex data model, with cross table links, i.e. foreign keys).

By choosing default (Table), the data is visible as a table on the selected location. It contains the list of default metadata as specified on the structure of the EDM plus the list of Enterprise Custom Fields you may have defined.

Using the extracted data

From the table, you can create for example a Pivot Table, to display the number of projects per status or per owner…

You can also combine Pivot Tables, Excel formulas and some kind of Design to create a custom report. This report could be displayed directly on your Project Server instance, using Excel Services.

Here is a dummy example of a small portfolio risk report. For this, I need the RiskId (for counting), the risk Status, the risk Impact and the risk Probability from all my projects.

Query is: http://<pwa_site>/_api/ProjectData/Risks?$select=RiskId,Status,Impact,Probability

Within my dashboard, I use Excel function COUNTIFS to count the number of active risks in each box.

Note that you might need to create pivot tables in order to make more complex calculation or use sliders for filtering data.

Some queries you might use

Here are some interesting queries you might need to use.

Note that the query is case sensitive; e.g. the red “and” on the third query, if you put “AND” it will not work.

A final note on the Security aspect

“When Project Server 2013 or Project Online is in Project permission mode, you can explicitly grant or deny access to the OData feed for specified Project Web App users. For example, on the Edit User page in Project Web App, expand the Global Permissions section, and then in the General section, select the Access Project Server Reporting Service check box in the Allow column.”

Source: msdn.microsoft.com