When Project Server 2013 first came out in an online and on-premises version, I began porting some of my demo reports over to the new online environment. The goal was to see what worked, and what didn’t work, and what new skills I would have to acquire to achieve reporting parity between the online and on-premises world.
In general, I had good success, although I had to come up with a number of workarounds to integrate the data. One of the reports I was working with, however, the Baseline Execution Index report, I could never get to work with the OData feed and Excel. The basic issue was that the underlying query for this report requires a UNION ALL command – essentially taking two different data sets and merging them into a master data set.
PowerPivot vs. PowerQuery
In PowerPivot natively, this is actually quite hard. What you need to do is assemble a master set of primary keys – then merge the two data sets into the master set. It sounds simple(ish) in theory, but it always eluded me. In fact, that seemed to be a flaw in PowerPivot (or in my understanding thereof) that UNION ALL procedures were tough, if not impossible to implement.
Hence, I decided that this report would make a good guinea pig for playing around with PowerQuery, a new tool for Excel that, as of this writing, is still in Beta. I’m still wrapping my head around how PowerQuery plays with PowerPivot, but the basic concept appears to be as a front end, i.e. PowerQuery is what you use to get the data into your report – with some reasonable formatting and massaging. PowerPivot is what you use to hook the information together into useful data models. That being said, it’s a bit of a blurry distinction as PowerPivot also can import data directly – just not as effectively or with all of the bells and whistles to make your life easier. (The UNION ALL issue being a case in point.)
Hacking PowerQuery Authentication
First thing I had to do was connect PowerQuery to the OData feed – which required a workaround using Fiddler documented by Peter Holpar here: http://pholpar.wordpress.com/2013/03/08/accessing-office-365-rest-services-using-linqpad. I’m hoping that as of RTM, this step would no longer be required.
Defining the Data Sets
The next thing we have to do is define our data sets. To create this report, we essentially have two data sets:
- The actual finish dates for the completed tasks in our schedules.
- The baseline finish dates for the tasks in our schedules.
The report works by totaling the number of tasks that are supposed to finish in a given month and comparing that to the total number of tasks that actually finished in the month.
Here are the two URLs I used to create the data sets. (ProjectNA is my somewhat unimaginatively named online tenant) These URLS were generated from LINQPad.
https://projectna.sharepoint.com/sites/pwa/_api/ProjectData/Tasks()?$filter=TaskActualFinishDate ne null&$select=ProjectName,TaskActualFinishDate
https://projectna.sharepoint.com/sites/pwa/_api/ProjectData/TaskBaselines()?$filter=BaselineNumber eq 0&$select=ProjectName,TaskBaselineFinishDate
I now fire up Excel and navigate to the PowerQuery. I select the option to import data from an OData feed. (Note the list of options to import data from – including Facebook….which is interesting but not entirely relevant for me at the moment.)
Paste the URLS we developed in the last section. (You’ll have to do this twice – once for each data set.)
In this case, I’m using Windows authentication via the Fiddler hack.
That should yield something like this:
Right click on each of the data sources to the right to modify the underlying query.
We’re going to add another field – which will be used to group the data. To do this, select the option to add a Custom Column in the top ribbon.
…which adds a new column to our query. I’ll call this “TotalDate.”
Repeat the same for the Actual Finish date query. Now we will combine the two tables into a single data set using the Append command on the ribbon.
It doesn’t really matter which table gets appended to which table.
And you now have a master data set. I add another column to this data set to capture our Target data within the chart.
Summarize the resulting data set into a PivotTable. Within the PivotTable, we’re going to add a new calculated field, BEISource:
This will generate the ratio of tasks actually finished against the tasks that were supposed to finish. In this case, it’s comparing the date value for the two items instead of the actual item count – which is more or less the same for our purposes.
Throw it into a column chart and format per the specs here, and you should now have something like this.