Insight

Insight from Agora Consultants

Querying Project Server oData

With Project Online, the developer has lost the comfort of the Reporting database. We can no longer write stored procedures or quick SQL statements to get the data we need without waiting for a lengthy service call. I had never used oData before, so the learning curve was large for me. This is by no means an expert’s guide to oData, you can find all of what I am saying here on other oData sites, but I am tailoring this to the Project Server developer.

To test my oData URLs I needed to turn off Feed Reading view in IE. I suggest you do the same.

  1. Go to Tools, Internet Options

  2. Under the Content tab, Feeds and Slices group, click Settings…

  3. Uncheck Turn on feed reading view

The first URL to hit of any oData feed is always the $metadata file. Try to hit this URL:
https://[tenant].sharepoint.com/sites/pwa/_api/ProjectData/$metadata.

You should get a big XML response listing all of the entities and the properties of each entity available in the feed.

NOTE if you get Access Denied (like below), hit the PWA home page so that you can login first, then try this URL again.

 

Loading a list of projects

This is the simplest starting point. Use this URL:

https://[tenant].sharepoint.com/sites/pwa/_api/ProjectData/Projects

In the result set above you can see that each project is represented by an “<entry>” XML tag. The first few child nodes are <link> nodes that show the oData URL to load the project’s Assignments, Deliverables, Tasks, Risks, Issues, etc. Then under <content>/<properties> is where you will see all of the project’s information, its built in fields and custom fields.

If you scroll down to the custom fields for one project (all nodes under ResourcePlanUtilizationType) you will see how the custom fields are referenced in the feed. It is not the internal name as CSOM uses, but the name of the custom field without spaces or special characters.

NOTE this is why the Agora PORT 365 app requires fields to be mapped to the oData feed, as the names of custom fields in this result are not obvious.

 

Filtering

To filter the oData call, one option is to use the $filter query string parameter:

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$filter=ProjectName eq 'Agora 001'

This will return an oData result only with projects that match this filter. Note the quotation marks used because ProjectName is a string.

To filter on GUID, you can do this:

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$filter=ProjectId eq guid'e2ea7447-23f9-4b39-80e4-dc9aa25a6a5c'

Simply add guid before the quotes and the string will be parsed into a GUID.

There are also special ways in which you can filter if you specify the entity’s key. Since ProjectId is the key for Projects, this query is also valid:

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects(guid'e2ea7447-23f9-4b39-80e4-dc9aa25a6a5c')

Filtering on key is the fastest way to drill deeper into the query. For example, let’s say you want all of the project’s tasks:

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects(guid'e2ea7447-23f9-4b39-80e4-dc9aa25a6a5c')/Tasks

Or Assignments!

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects(guid'e2ea7447-23f9-4b39-80e4-dc9aa25a6a5c')/Assignments

You can drill down into any entity that is listed as a <link> in that $metadata file you visited at first. If you scroll back up to my screenshot now, you will see the links to Tasks and Assignments look exactly like the examples I have just shown.

I would like you to run each of the queries I have listed above. You will likely have to parse the XML result in code. Whether you are querying projects, tasks, assignments or anything else, the information you are looking for will be in <entry>/<content>/<properties>. Keep this in mind if you get lost.

Further, you can even filter on date, but be warned, the dates returned in the oData feed use a 24 hour clock, not AM/PM. So if you need to make a call from code like this:

string query = string.Format( "/ProjectData/Timesheets?$filter=(StartDate ge  datetime'{0}' and StartDate lt datetime'{1}')",

start.ToString( "yyyy-MM-ddTHH:mm:ss" ),

end.ToString( "yyyy-MM-ddTHH:mm:ss" ) );

 

Make sure the hours are upper case (HH). Also worth noting that the ge stands for “greater than or equal to” and the lt stands for “less than or equal to”.

Boolean (or flag fields as Project Server calls them) must be filtered like so:

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$filter=testprojectflag eq true

Notice how true is in lower case and is not in quotes. When I have researched this in the past, many forums say you need to use 1 and 0 as true and false, but this is not the case with Project Server. You specify true or false, without quotes.

Select

Another useful query string parameter is $select. Use this to only return the fields you need. Some examples below:
https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$select=ProjectId,ProjectName

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$filter=testprojectflag eq true&$select=ProjectId,ProjectName

There is not much to explain here. Select will only return the nodes that you specify. If you use select (or any oData query string parameters) together in the same URL, remember to use & instead of ?.

Skip & Top

The last thing I will mention is paging. If you are not aware, different entities in the oData feed have record limits. You can see a current record of the limits in this MSDN article https://msdn.microsoft.com/en-us/library/office/jj163015.aspx.

So what if you have 1000 projects? How do you get them all? Well, this is how:

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$skip=0&$top=200

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$skip=200&$top=200

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$skip=400&$top=200

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$skip=600&$top=200

https://[tenant].sharepoint.com/sites/psqa/_api/ProjectData/Projects?$skip=800&$top=200

You need to make 5 different calls. The first call says “Give me the first 200 projects”, the second says “skip the first 200 projects, and give me the next 200”, and so on. In code, you will have to keep adding 200 to the skip query string, until you get a call with no more results.

Conclusion

The biggest barrier to entry is understanding how to parse oData results. Building the queries and URLs is not hard, especially if you have read and understand this article. Just remember developers, when it comes to oData CASE DoEs MATTER!



 
Comments are closed