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 { p.ProjectId, p.ProjectName }
…which, when dropped into LINQPad, yields the following URL:
http://demo/PWA//_api/ProjectData/Projects()?$select=ProjectId,ProjectName
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 { ts.TimeByDay }
…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 { t.ProjectId, t.TaskId, t.TaskFinishDate, t.TaskActualFinishDate }
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 { tb.ProjectId, tb.ProjectName, tb.TaskId, tb.TaskBaselineFinishDate }
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.
