More Timephased Reporting with OData

I realized that yesterday’s post may have given the incorrect impression that you need to go through all sorts of mind bending contortions to get timephased reporting out of Project Online.  The reality is that you’d only have to limber up if you wanted to do reporting like I showed yesterday, i.e. a detailed view of all projects and capacity by resource and time.

The reporting gets a lot simpler when you’re looking for less data.

For example, if I don’t want to parcel out each project separately, I could use the same set of feeds I created in yesterday’s post:

Resources:

http://demo/pwa/_api/projectdata/Resources()?$select=ResourceId,ResourceName

Assignments (or Demand):

http://demo/pwa/_api/projectdata/AssignmentTimephasedDataSet()?$filter=TimeByDay ge datetime’2013-01-01T00:00:00′ and TimeByDay lt datetime’2014-01-01T00:00:00’&$select=ResourceId,ProjectName,TimeByDay,AssignmentCombinedWork

Capacity:

http://demo/pwa/_api/projectdata/ResourceTimephasedDataSet()?$filter=TimeByDay ge datetime’2013-01-01T00:00:00′ and TimeByDay lt datetime’2014-01-01T00:00:00’&$select=ResourceId,TimeByDay,Capacity

…from there, I would need to add an additional query to get the TimeByDay dimension:

http://demo/pwa/_api/projectdata/TimeSet()?$filter=TimeByDay ge datetime’2013-01-01T00:00:00′ and TimeByDay lt datetime’2014-01-01T00:00:00’&$select=TimeByDay

Put those together in PowerPivot, and you should have a data model that looks like this:

To simplify the charting process, I’ll add a field to the TimeSet table summarizing the data by month:

From there, it’s just a simple matter of creating a PivotTable and a bar chart:

Moral of the story: simplify the reports to reduce the effort required to create them.  This report was pretty easy to develop, but doesn’t have all of the individual projects displayed in the stacked bar chart.

To simplify things, I can add that detail as a second chart on the same worksheet using the data feeds we already created.  Overall, I’d argue that we don’t lose a whole lot in terms of experience by reducing the information density of the overall dashboard.