Il est bien plus beau de savoir quelque chose de tout que de savoir tout d'une chose. [Blaise Pascal]

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

4 comments
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.
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.”

4 comments :

  1. Weabers Inc. is focused on the future of the web. We are a fully integrated design and technology company that transforms ideas into future-proof digital experiences, and help our clients reach the next step in their digital evolution.
    let know here > weabersinc

    ReplyDelete
  2. This is a standout amongst other administration article I perused today. Wonderfully composed. I extremely enjoyed it. Continue posting these stuff. Visit my site Estudio de Clima Laboral

    ReplyDelete
  3. Thanks to shearing this post. This blog is amazing and interest to read your blog.

    Food delivery application is certainly one of the vital apps that any food lover fans will have on their smartphones.
    There are millions of food lovers who are just incorporate food into their lifestyle.
    Nowadays, there are tons of useful mobile apps that are literally dedicated to food.
    Right from piling up recipes, watching someone cook to ordering online food, there are food delivery apps for every foodie lovers.
    What if you are craving for foods in the midnight, Foodgram will find you through your location to solve your late-night cravings. Yes! Foodgram is the first and best solution in this food-industry to solve your late-night cravings.
    Now you can have your desire pizza or if you want to cherish your mood by having some desserts or some pastry from your nearest restaurant, Foodgram will be only one call away.
    Share your food stories through Foodgram’s Instagram and avail your points to have discounts on your next food order.
    To have a first free home delivery you can pay a visit to Foodgram, no wonder you can be the first luckiest one who will get a free coffee from your favorite coffee café!

    ReplyDelete
  4. We have established our own product development company Weabers because we believe that there is always a better way to do things. We are truly passionate about experiences, user interfaces and developing the best possible digital products.

    ReplyDelete

Note: Only a member of this blog may post a comment.