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.

image

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

image

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:

image

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

image

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

image

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.

image

Advertisements
More Timephased Reporting with OData

5 thoughts on “More Timephased Reporting with OData

  1. Hi Andrew,
    I’ve read both your last posts and am truly impressed by what you’ve pulled together. I will spend some time to repro in the environment I use for customer POC.
    But like you said, I’d be reluctant to use your ‘capacity project’ hack in a production environment. Hence I’d be reluctant to usher customers to Project Online as of now.
    I really believe the product team should create an oData feed that contains both Assignment Timephased Work, Resource Timephased Capacity and include every Project and Resource Enterprise Custom Field (e.g. Project State, Resource Contract Type).
    Oh, and while we’re at it, Tasks and Assignments should all inherit Project custom field values by default :-).
    Cheers,
    Bram

  2. Michael says:

    Andrew,

    Thank you for this and your previous post. This is a capability that is sorely lacking from Project Online, and I hope Microsoft uses your example to build a similar capability into their base offering.

    I have tried both of your solutions and have come up with the same two problems in both. The first, I am sure you have a quick answer for: When I try to create a calculated field in PowerPivot (in both the TimeSet and Assignment Timephased Dataset, I receive the following error:

    “Calculation error in measure ‘TimeSet'[Month]: The value for ‘TimeByDay’ cannot be determined. Either ‘TimeByDay’ doesn’t exist, or there is no current row for a column named ‘TimeByDay’.” Can you tell me what I am doing wrong?

    The other has to do with performance. I created both of the above connections using Project Online and the Contoso sample data, and it took several minutes to refresh each of the datasets. Apparently despite the filters built into the queries, it must walk through every record. Not sure if there was a way around this.

    Thanks again,
    Michael

    1. Not sure about the first, although I would suspect that maybe you just haven’t named the column correctly? Or are you using a non-US English version of Project Server (as the columns get localized, I believe)?

      As for performance, I suspect that’s pretty much the way it is. As of yet, I don’t know of any performance improvement suggestions for OData. It would make a nice post though. 🙂

    2. David Blair says:

      Hi – If you copy\paste from the blog you might get this error because the quotation marks are not valid for use in a formula. I found that if I copied to Notepad first and just replaced the quotes with the Notepad version, it worked fine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s