Porting the Cumulative Milestone Report into OData (Part 2)

In this post, we’ll create the appropriate data connections to pull the data from Project Server OData into Excel 2013.  To do this, we first need to do a bit of analysis on the required data.  As we already have a working version of the report using SQL queries, we can break down the technical requirements relatively easily.


Establishing the Dimensions

First off, we need to identify how we’re going to slice and dice our data, and then ensure that we have that data available to us.  For this report, we’ll ensure we have the following dimensions available:

  • Project Name
  • Project UID
  • Total Date

Total Date is a field that we will create that displays the beginning of the month for any given date.  In this case, we have two data sets that we need to map to the same month: the task baseline finish date, and the task forecast finish date.  Essentially, we’re going to count how many tasks were baselined to finish in any given month, and then compare that to how many tasks are forecasted to finish in that month – as well as any tasks that actually completed in that month.

If you’re tracking your schedules more granularly, you may want to modify the Total Date field to the beginning of any given week or day within the target time span.  (For example, when I was last tracking an ERP go-live schedule, we modified our reports to group everything to the day.)

Each of the dimensions is captured from an OData feed.  Below, I’ll show how I developed the feed in LINQ, and then what the resulting URL was.  As I mentioned in the last post, you can skip the LINQ development and simply modify the data in Excel PowerPivot, but it was quicker to just add the filters and select the fields in the query itself.

Project Name

Let’s create a table with the project name.  We can use this field to filter our report at the project or program level.

Here’s the LINQ query….

from p in Projects
select new

…which, when dropped into LINQPad, yields the following URL:


Total Date Dimension

We also need the first day of the month within which any work may occur.  We can do that using the TimeSet feed.  That contains a list of the days on which work may be scheduled within Project Server.  I filter out every value but the beginning of the month.

from ts in TimeSet
where ts.TimeByDay.Day == 1 && ts.TimeByDay > new DateTime(2011,1,1) && ts.TimeByDay < new DateTime(2015,1,1)
select new

…and the URL:

http://demo/PWA//_api/ProjectData/TimeSet()?$filter=day(TimeByDay) eq 1 and TimeByDay gt datetime’2011-01-01T00:00:00′ and TimeByDay lt datetime’2015-01-01T00:00:00’&$select=TimeByDay

Establishing the Measures

We now need to add two more data sets to provide the baselined finish dates vs. the current forecasted finish dates.  We need to split these up into two data sets because we may have had a task that was baselined to complete in March, but is now scheduled to complete in April.  Hence, we need to have the task show up in both months in the correct columns.  In SQL, we could handle this using a UNION command.  With PowerPivot, we’ll take a slightly modified approach.

Forecast & Actual Finish

Forecast & Actual Finish are fairly straightforward.  We’ll get them from the Tasks table.

from t in Tasks
where t.TaskDuration == 0 && t.TaskFinishDate != null
select new

http://demo/PWA//_api/ProjectData/Tasks()?$filter=TaskDuration eq 0M and TaskFinishDate ne null&$select=ProjectId,TaskId,TaskFinishDate,TaskActualFinishDate

Baseline Finish

The Baseline Finish field is treated a bit differently in OData than in SQL.  In SQL, I have a nice table with all of my task data to pick and choose from.  In OData, the baseline data is pushed off into a table by itself, i.e. I have one field for BaselineFinishDate – and 11 different rows for Baselines0-10..  This necessitates adding a filter to only pull baseline data for Baseline0.  For the moment, I am filtering for milestones by only selecting those rows with a baseline duration of zero.

from tb in TaskBaselines
where tb.BaselineNumber == 0 && tb.TaskBaselineDuration == 0
select new

http://demo/PWA//_api/ProjectData/TaskBaselines()?$filter=BaselineNumber eq 0 and TaskBaselineDuration eq 0M&$select=ProjectId,ProjectName,TaskId,TaskBaselineFinishDate

Add each of the OData feeds to Excel through the Data > Add Other Sources option:


When prompted, select the option to Only create the connection.


Next up….creating the data model in PowerPivot.

Porting the Cumulative Milestone Report into OData (Part 2)

Porting the Cumulative Milestone Report into OData (Part 1)

As part of my preparation for the upcoming SharePoint Conference, I decided to embark on an exercise to port some of my more popular reports over to OData and Project Server 2013.  Indeed, it turned out to be quite the mental exercise – and hopefully I didn’t end up with the cranial equivalent of a stress fracture.  Not that, in retrospect, it was hard.  I think it was more difficult to get my head around the new concepts and models required than actually doing the work.



So first off, a macro level view of things.  Where the 2010 business intelligence story was really about empowering the end users, the 2013 story, is basically that – and more.  The critical difference in 2013 is that instead of simply presenting a massaged data set to the end users in the form of data connections, and then allowing the end users to develop reports; now, we’re presenting the raw data to the end users and then allowing them to create the data model in Excel.

In fact, when you start looking at Excel 2013, you can see substantial investment has been made in PowerPivot, and in porting a rudimentary SQL query design editor UI into Excel.  I can now take the raw data from SQL and then manipulate it at a base level in Excel before even bringing it into the familiar Excel worksheet interface.

The report I decided to start with was one I originally discussed here.  It’s relatively uncomplicated, i.e. it doesn’t require custom fields, but it does require a combination of baseline data, and some fancy footwork to compile multiple data sets.  For those of you reading this with on-premises installations, feel free to use those instructions as you’ll have direct access to the SQL data repository.

These instructions are only for those who have their data in the Project 2013 cloud based model –or- for some reason have determined that you’d like to pursue using the OData interface to consume project data.

Assembling Our Toolbox

There’re a couple of tools that we’ll use to develop this report.  Primarily, we’ll be using Excel 2013.  Out of the box, Excel 2013 can now import data from OData feeds – whereas this required the install of the free PowerPivot for Excel add-in for Excel 2010.  PowerPivot comes installed as an optional add-in, which we need to activate to modify the data model.  So let me state this very clearly: you don’t need PowerPivot installed to consume OData, but if you want to build relationships between the data tables and manipulate the structure, you’ll need to activate the PowerPivot add-in.

You’ll find it under Options > Add-Ins > Com Add Ins.


I would assume that these instructions should work equally well in Excel 2010 with PowerPivot, but haven’t validated that.  Feel free to add your experiences to the comments below.

The second tool would be a working instance of Project Server with the OData feed validated.  Navigate to the appropriate URL to confirm that you see the right data.




Finally, it’s not really a requirement, but I found it helpful to have a working instance of LINQPad available.  LINQPad is a free tool that you can use to generate LINQ queries and then to turn those queries into a URL to query OData.  You don’t need LINQPad, but I found it saved a bit of time, i.e. I could filter the result set and select the fields before pulling them into Excel.  Otherwise, you need to pull them into Excel, and then select the columns.  If you’re comfortable with simple SQL queries, you’ll probably just find it easier to specify the fields in the query itself.

Next up….setting up the data connections within Excel.

Porting the Cumulative Milestone Report into OData (Part 1)

First Look: Querying Project Server 2013 OData with LINQPad

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:


…meaning that the PWA site at http://demo/pwa would have an OData feed at http://demo/PWA//_api/ProjectData/.

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.

First Look: Querying Project Server 2013 OData with LINQPad

Introducing the Southeast Conference Series 2012

UMT (and yours truly) are taking to the road to spread the gospel of project & portfolio management.  We’ll be in Tampa, Atlanta, and Charlotte in the coming weeks.  Come on out.



Microsoft® Enterprise Project Management

Southeast Conference Series 2012

DON’T MISS this opportunity to learn from the industry’s most progressive companies on the benefits of Enterprise Project and Portfolio management!

Raymond James:Keeping it Simple and Driving Results!  The Secrets of Success for an EPM 2010 Migration

Ingersoll Rand:A true global PPM implementation.  How Ingersoll Rand implemented Microsoft EPM 2010 to over 4000 users.”

AGCO Corporation:Committing to Microsoft EPM.  The road to implementing Microsoft EPM 2010 at AGCO: the past, the present and what the future holds

Join Microsoft and Project Portfolio Management industry leaders for a day of valuable and useful information on Microsoft Project 2010 including customer keynotes, business and technical presentations, lunch and a preview of what’s is next for Microsoft Project Server. 
Please choose a convenient date and location and click the button to register.  More information and complete agendas are available on the registration pages.

October 11

8:30am – 2:00pm

clip_image004Microsoft – Tampa

5426 Bay Center Dr., Suite 700
Tampa, Florida

October 23

8:30am – 2:00pm

clip_image006Microsoft – Charlotte

8050 Microsoft Way
Charlotte, North Carolina


October 26

8:30am – 2:00pm

clip_image008Microsoft Office – Atlanta
1125 Sanctuary Parkway

Alpharetta, Georgia



Microsoft® EPM

Southeast Conference Series 2012



Event ID: 1032527159

Thursday, October 11, 2012


Event ID: 1032527174

Tuesday, October 23, 2012


Event ID: 1032527175

Friday, October 26, 2012

Products: Microsoft Office Enterprise Project Management Solution, Microsoft Project 2010 and Microsoft Project Server 2010

Meal: Breakfast & Lunch

Introducing the Southeast Conference Series 2012