As I gradually immerse myself into the world of Project Server 2013, one of the major changes I’ve been forced to come to grips with is the new method of querying Project Server data through OData. OData is now the preferred mechanism to surface cloud based data, and is designed to replace direct access to the SQL database.
To access Project Server OData feeds, simply add this to your PWA URL:
The results look something like this, i.e. pretty much like an RSS feed:
In fact, one of the tricks you’ll pick up after working with OData is turning off the default Internet Explorer RSS interface, which tends to get in the way of viewing OData feeds. Access that via the Internet Explorer > Internet Options page.
I can also consume OData feeds directly in Office applications such as Excel. In Excel 2013, I now have the option to connect to OData directly…
That yields the table selection which I may then use to develop my reports.
More on that topic in later posts. In this post, I want to talk about writing queries against OData using LINQ a querying language that some of you are probably familiar with. I would hardly call myself an expert, but I’ve found the easiest way to get up to speed is to download and install LINQPad, a free query writing tool.
With LINQPad, I can teach myself LINQ, following a simple step by step tutorial.
…and then point LINQPad at a hyper-V image of Project Server to test my queries.
…even better, LINQPad generates the URL that I’ll need to customize the OData feed from Project Server:
I.e. the query above to select the Project Name and Project Start from all projects starting after 1/1/2013 yields a URL of:
http://demo/PWA//_api/ProjectData/Projects()?$filter=ProjectStartDate ge datetime’2013-01-01T00:00:00’&$select=ProjectName,ProjectStartDate
Minor caveat to this approach: out of the box, LINQPad doesn’t authenticate to Office 365 tenants. It looks like other folks have already figured out a solution to this, which I haven’t gotten around to deciphering on my own. In the meantime, LINQPad works fine against on-premises installations. For now, I’ll probably be developing my queries against an on-prem data set, then applying the URLs to my Office 365 tenant.
For example, using my online tenant, I can parse the following URL:
https://lavinsky4.sharepoint.com/sites/pwa//_api/ProjectData/Projects()?$filter=ProjectStartDate ge datetime’2012-01-01T00:00:00’&$select=ProjectName,ProjectStartDate
…and get the correct results. Here’s what it looks like in Excel:
Coming up….porting some of my previous report queries into LINQ.