Porting the Cumulative Milestone Report into OData (Part 3)

Now we have an Excel workbook with the appropriate data connections to Project Server….

image

Let’s break out PowerPivot to modify the data model.  Click on the Manage button in the PowerPivot tab.

image

You should see something that looks like this:

image

Rename the tabs to something a bit more useful.

image

Now let’s add a custom field to the Forecast and Baseline tables.  We’ll use this field to map the data back to the TimeSet table.

I used this formula for the Forecast table:

=[TaskFinishDate]-Day([TaskFinishDate])+1-TimeValue([TaskFinishDate])

..and name the new field “Total Date.”

image

I repeat the process for the Baseline data set, but use the TaskBaselineFinishDate value to generate my Total Date field.

image

Now let’s tie it all together.  In the Home tab, select the Diagram view.

image

Add the following joins to the data model.

image

If I select the option to Manage Relationships in the Design tab, I should see the following:

image

At this point, we simply need to add a pivot table to Excel and point it at our data model.

image

Our data model appears as a table in the External Connections list.

image

…from there, you need to add the appropriate fields to the pivot table.

image

Note that I added each of the date fields twice.  I’m going to flip the second instance of those fields to display a running total and give them a more user friendly name…

image

From here, it’s a matter of following these instructions to create a chart.

image

Not entirely sure the juice was worth the squeeze on this particular chart, but still a worthwhile exercise.  Add the slicer and timeline control for extra cred as an Excel 2013 master.

image

Advertisements
Porting the Cumulative Milestone Report into OData (Part 3)

11 thoughts on “Porting the Cumulative Milestone Report into OData (Part 3)

  1. Hi Andrew,
    I truly appreciate this post, but I have trouble getting my head around this. Do you think I need to jump through all these kinds of hoops to create a report where timephased assignment work is compared with timephased capacity for (filtered) list of resources? Or maybe you are aware of a sample report based on OData that’s available somewhere?
    Cheers,
    Bram

    1. I kind of see what you mean. I tried to put together a timephased report including Capacity over the weekend, and failed. I can get a stacked bar chart put together pretty easily with minimal hoops, but trying to add a Capacity line has stumped me. The issue, which is probably not unsolvable, is that you need to basically use the equivalent of a SQL UNION ALL command to combine ResourceTimePhased with the AssignmentTimePhased feed – and I haven’t figured out how to do that (and not crash Excel).

      It’s a bit of a hack, but I think I can do it by creating a project called “Capacity,” assigning every resource to their max units for the year, and then using that for my capacity calculations.

      1. Update – it might be a matter of managing your own expectations. Simplify the report requirements, and it’s actually pretty easy to develop timephased reports. Check my next two blog posts (3/25 and 3/26) for a couple of examples.

  2. Hi Andrew,
    I agree that a report with timephased from 1 ‘container’ is pretty straightforward. If I’m not mistaken Project Server 2010 had an OLAP cube called ‘Portfolio Analyzer’ which did combine TP work and capacity. I think it’s reasonable customers expect similar information to come through Project Online.
    Will take a look at your new blog posts, thanks!
    Cheers
    Bram

  3. Fred says:

    Andrew,

    First ,thanks very much for this. It is almost exactly what I want and was instrumental in getting me to the point I am at. Currently, I am using PowerPivot in Excel2010 and pulling MS Project data. I want to create a similar chart, however I am only showing cumulative BSLN Finishes and cumulative Actual Finishes. I have three tables, TimeByDay (to eventually group by Fiscal Period), Task_UserView (for Actual Finishes) and another Task_UserView basis table (for Baseline Finishes). My issue is that I want to add a cumulative BEI line (total finishes for a fiscal period / total planned (BSLN finishes) for the same fiscal period. Since you cannot add calculated fields directly to the pivot table, this means one would have to add a measure in PowerPivot. I have not been able to figure out which table to put it in or how to get it to calculate and show the correct value in the chart. Any ideas?

    Thanks again…this 3 part series was absolutely great!!!

      1. Fred says:

        Sorry for the delayed response…yes, I did look at that, but I believe you are utilizing a standard pivot table in that example, which allows you to add the BEI calculated field directly within the pivot table…whereas the ‘PowerPivot’ pivot table does not allow you to add calculated fields…We’d have to add a calculated measure within PowerPivot which is then added to the pivot table, that’s the piece I haven’t figured out yet. Utilizing Power Query is an option, but I’m wanting to keep the number of add-ins to a minimum and keep things as simple as possible so that others can pick it up and understand / maintain it.

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