Atlanta Presentations Follow Up

Thanks to everyone who attended my two presentations at the Atlanta Microsoft Project event held last week in Alpharetta.  As promised, here’s some follow up information (organized more or less by topic):

-skip to the bottom for a list of resources for newly minted Project Server admins-

Project Server Business Intelligence

Finally – the white paper on TechNet that talks about each of the potential methods.

Team Foundation Server Integration

Lastly, I chatted with a couple people about how to bring a new Project Server administrator up to speed.  Please see this post for a list of recommended resources.

Atlanta Presentations Follow Up

Rolling Up Physical Percent Complete

A couple of weeks ago, I kicked off a bit of an exercise in personal improvement by revisiting my own understanding of how Microsoft Project calculates Actual Cost and Earned Value measurements.  This post represents a continuation of that effort, as I explore how Physical % Complete rolls up from child to summary tasks.  I’ll admit up front, until I began writing this post, I didn’t realize that Physical % Complete actually rolls up natively within Microsoft Project.

Physical % Complete

First off, let’s take a step back and talk about just what exactly Physical % Complete is.

Physical % Complete is a measure of how “done” a task actually is.  Generally speaking, it’s the most appropriate measure of how done the task is – especially when compared to other typical measures like % Complete or % Work Complete – which track how much duration and work we’ve consumed respectively.

Physical % Complete is intended as a physical measure.  For instance, when building a 10 mile pipeline, after building 5 miles, I would call the task “50% complete” in Physical & Complete.  In software, I would identify rules of credit to define when I am allowed to change Physical % Complete.  For instance, an architectural review may net me 25%, architect approval 50%, customer sign off 100%.  The goal is to make sure everyone understands when and how the credit is applied.

% Complete only tracks how much time I spent to get to that level of doneness.  If I spent 70% of my planned duration building that pipeline, I would be 70% complete within the % Complete field.

Generally, when using these fields, we want to employ the concept of triangulation, i.e. each one of those variables is not important on its own – but powerful in combination.  In the above example, the two points of data for that pipeline project would imply I burned 70% of my duration to complete 50% of my work.

The Mechanics of Rolling Up Physical % Complete

The first thing I might point out is that I was always under the impression that it didn’t and that each child task was independent in how the value was calculated.

In this project, in fact, it doesn’t roll up.


A funny thing happens though when we add the Earned Value Calculation Method field into the mix and toggle it to Physical % Complete on baselined tasks.


Physical % Complete does roll up….but only when all tasks have been flagged to use the Physical % Complete method – and have also been baselined.

Here’s what happens if we leave one task flagged to use % Complete:


None of the relevant subtasks roll up to a common parent task any more. 

The next step is to figure out just what calculations are occurring.  As far as I can tell, we have a simplified form of Earned Value working here.  Going back to the original example:


…we can replicate the calculations by multiplying the Physical % Complete X the baselined cost of the task.

  Baseline Cost Physical % Complete Earned Value Calculation
Project Summary Task $13,200 6%   $750/$13,200
Summary1 $2,000 38%   $750/$2000
Task 1 $1,000 50% $500  
Task 2 $1,000 25% $250  

Great.  So now we figured this out.  What do we do with it?  Honestly, I am not entirely sure.  But at least it’s out there, and I can refer to it as I come back to coach my client through the various EVMS calculations.

There’re also a couple other salient points as I see it:

  • Physical % Complete may not be edited in summary tasks.  If we wish to manipulate Physical % Complete on summary tasks, we would need to ensure that all subtasks have cost assigned and are kept updated.
  • In environments where we do not plan on tracking cost at the lowest level, we are effectively required to use % Complete as an Earned Value calculation method.

Both good things to note….

Rolling Up Physical Percent Complete

Developing a Baseline Execution Index Report

Well, some of you have been reading this blog for nigh on 4 months, and through this medium, I feel that we’ve built a solid and trusting relationship.  Hence, it’s probably about time for me to admit something rather embarrassing.  Just between you and me (and the search engines), until recently, I couldn’t write a SQL query to save my life. 

Now, I know what you’re thinking: “That’s no big deal.”  In the real world, not being able to write a SQL query is perhaps not a big deal.  But in the world of EPM tools, that’s tantamount to admitting that you’re an adult illiterate. (And that’s not to disparage any illiterate adults who may, in fact, be reading this post.)

Well, I am pleased to say that I have joined the ranks of the literati again after taking some time over the weekend to pick up some basic query writing skills.  I wouldn’t claim to be an authoritative expert in the field, but at least now I am somewhere in the advanced bumbler level.

Honestly, it was a great experience.  Everyone should learn SQL queries.  I can’t wait till my kids are old enough to want to learn so that we can bond over writing queries.  Looking at some existing queries that other folks have made for me, it kind of feels like the first time I walked into a restaurant in Seoul after having taught myself to read Hangul.

So what to do with this newfound knowledge?  Why, develop some slick portfolio reports, that’s what!

Hence today, I present to you the Baseline Execution Index (BEI) Report.



The BEI Report is something that I’ve stumbled across before and is actually required in a lot of military contracting.  In fact, if you plug BEI into your search engine of choice, you’ll see a number of references to it on the US Defense Acquisition University Website. (See here)

Essentially, it’s a performance measure on the project (or portfolio) that totals up all of the tasks actually finished in a given period, then divides that number by the total of all tasks scheduled to finish in the given period.  That results in an index:

(Number of Tasks Actually Finished / Number of Tasks That Should Have Been Finished)

In a perfect world, your index would be hovering right around 1.  That means you are completing all of your tasks more or less on schedule.  A BEI greater than 1 would indicate that you are completing more tasks each month than were baselined – and a BEI of less than one would indicate that you’re completing less tasks than were planned.

This is another one of those reports that could be used at the portfolio level to indicate portfolio performance or at the individual project level to indicate project performance – both reports doable with the relatively low administrative overhead of simply requiring a baseline and a reasonably decent schedule update process.

The Query

Drop this query into an appropriate ODC to get the required data set:

SELECT     dbo.MSP_EpmProject_UserView.ProjectName, NULL AS BaselineFinish,
                      TaskActualFinishDate AS ActualFinish, Set1.TaskActualFinishDate AS TotalDate
FROM         dbo.MSP_EpmTask_UserView AS Set1 LEFT OUTER JOIN
                      dbo.MSP_EpmProject_UserView ON Set1.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE     (TaskBaseline0FinishDate <> 0) AND (TaskActualFinishDate <> 0)AND (TaskActualFinishDate < GETDATE()) AND TaskIsMilestone = 0 AND TaskIsSummary =0   
SELECT     dbo.MSP_EpmProject_UserView.ProjectName, Set2.TaskBaseline0FinishDate AS BaselineFinish, NULL
                      AS ActualFinish, Set2.TaskBaseline0FinishDate AS TotalDate
FROM         dbo.MSP_EpmTask_UserView AS Set2 LEFT OUTER JOIN
                      dbo.MSP_EpmProject_UserView ON Set2.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE     (TaskBaseline0FinishDate <> 0) AND (TaskBaseline0FinishDate < GETDATE()) AND TaskIsMilestone = 0 AND TaskIsSummary =0   
Order By TotalDate

…which essentially is a modified form of the query I used last week to generate my Cumulative Milestone Report.  What we’re doing is concatenating two sets of data, the Actual Finish dates for specific tasks as well as the Baseline Finish dates for tasks.

Note that the following filters have been applied:

  1. Only baselined tasks are included.  This ensures that we adhere to the current approved plan and ensures that project managers will not game the system by adding their own tasks.
  2. The Actual Finish Date and/or Baseline Finish Date is/are less than today’s date.  We shouldn’t be reporting on Actual Finish Dates in the future because, well, there shouldn’t be any, and if there were, they would indicate the project manager requires additional training rather than a bonus for finishing early.
  3. Milestone tasks are excluded.  As they do not indicate work and often are used to manage external factors, they should not be relevant to this exercise.
  4. Summary tasks are excluded.  Including them would invalidate the data by introducing duplicate record sets.

If you followed that query I wrote last week, you’ll note that I didn’t bother to convert the TotalDate field to the first of each month.  I intend to use the Excel grouping functionality to do that.  Since I don’t plan to implement any cumulative line graphs in the Excel chart, that shouldn’t be an issue.  (Although if you did plan on adding some sort of cumulative task completion plot line, I would recommend you take a look at that post.)

The Chart

Open Excel and add the connection to the ODC you configured with the SQL query above.  Insert a PivotTable based on that data and configure it as follows.


Now we’re going to add two calculated fields.  Click on the PivotTable and select the Options tab.  Click on Fields, Items & Sets to generate the two fields.

Call the first field something like “BEISource.”  Configure as follows:


Now we’ll add a “TargetSource” field.


Your PivotTable should look like this:


Rename the fields to something more user friendly.


Group the data by clicking on the Total Date field and selecting the Group button under the Options tab.  Group by Month and Year (or Quarter or whatever you would like).


Now select the option to create a PivotChart.  Use the column chart format.  You should now have something that looks like this.


Right click on the Target series, and change the chart type to line.  This gives us a nice benchmark to measure against.


Right click on the Y axis and choose the option to Format the Axis.  Set the options as follows:


Configure the X axis as follows:


And you should have something that looks like this:


Not a very compelling picture, for which I blame the sample Contoso data.  When I filter on a single project with better data, I get something like this:


Looking at this illustration, I would conclude that we were getting about half the work done that we thought we would between April and June, then were able to pick up some of the slack in July followed by a slowdown again in August.

…and now if you’ll excuse me, I plan to head out and solve the problems of the world with well-written SQL queries….

Developing a Baseline Execution Index Report

Houston Techfest Coming Up on October 15th

In Houston and looking for some free SharePoint content?  Houston Techfest will be offering two SharePoint tracks at the all day free conference held over at U of H next month.

…also lots of other content for developers and other IT types.

Houston Techfest Coming Up on October 15th

Developing a Cumulative Milestone Report (Part II)

In the last post, I reviewed a SQL query that may be added to an ODC file to generate the required data set.  In this post, I’ll talk about how to configure the pivot table within Excel to generate a cumulative milestone report as depicted below.


Once you’ve got your ODC file configured with the SQL query in that last post, go ahead and create a new pivot table.  Configure the pivot table so it appears as follows….


Note that I added each of the date fields twice to the Values section.  We’re going to use one instance of the field for our bar chart, and the second instance of each field to track our running totals.

Click on the first instance of each field in the Values section, select Field Settings, and change the name to something more friendly.


Do the same for the second instance of each field, but this time also change the value to show as a Running Total. 


You should end up with something like this:


Your pivot table should now look like this…


Click on the pivot table and select the option in the ribbon to generate a pivot chart.  Choose a column chart.


The result will look like this…


Now we have to convert our cumulative values to a line chart and apply them to the secondary axis.  To do this, click within the chart on the tall bars representing the cumulative values.  Select the option to Change the Series Chart Type.


Pick the line chart option.


The chart may now look something like this…


Click on each line element, right click, and then select the option to Format the Data Series.  Set the option to display on the secondary axis.


The chart may now look something like this:


Filter on the TotalDate field to identify the range you would like to see.  I’ll focus on the months in the later part of the data set.


My conclusion from looking at this graph…starting in August 2011, we seemed to have a number of planned and baselined milestones that were never completed.  As a result, a number of milestones were probably pushed out, as evidenced by the fact that my Cumulative Planned is greater than the Cumulative Baselined.

Even better…convert the report from a portfolio report to a project report by selecting a single project in the drop down list.


Not bad.  Even some team members of a specific Twitter follower who shall not be named would agree that this is pretty easy.  Toss it onto SharePoint and leverage the REST.API to automagically update the chart onto project reports.

Developing a Cumulative Milestone Report (Part II)

Developing a Cumulative Milestone Report (Part I)

Well, I got some very good feedback from that post I did a couple of weeks ago, and decided to throw some more BI examples at the wall to see what sticks.  This post kicks off a twoish part series on developing a Cumulative Milestone Report at the project, program or portfolio level.


A Public Service Announcement

Before we get immersed in technical detail, we should probably step back a bit so that I can point out to you that your organization is very likely discriminated against by many of the EPM tool vendors.  “Huh,” you ask?  “How can that be?”

Well, if you’re reading this you’ve probably implemented some sort of EPM tool to support your internal project management processes.  Chances are that those processes are still in an early stage of development.  From a BI perspective, that means that you’re probably shut out of all of the fabulous eye candy that your executives saw in the demos when the tool was first adopted.

Face it, it’s an open secret in the implementing community that all of those cool reports and drill downs are predicated on actually having data and/or metrics to display, data such as cost or effort tracking figures that get pumped into OLAP cubes on a nightly basis.

But what about the rest of us?  What about the organizations that are still tracking milestones and schedule variance and well, that’s pretty much it.  And trust me when I say that if you have to start somewhere, simply tracking milestones and schedule variance is a massive step forward for many groups.

Well now there’s hope.  BI eye candy can be yours as well.  In previous posts, I discussed a portfolio timeline report and a stage gate report.  In this post, I’d like to introduce a cumulative milestone report.  Sit back, enjoy, and know that you too can generate those exciting, catchy BI reports that were promised in the original demo.

….But Seriously

In all seriousness, there has been a noted paucity of compelling schedule driven reports in the marketplace of EPM ideas.  There are bright spots however, one of them being the slew of reports available from the Decision Edge add in.  In fact, typically when my clients start asking about options for reporting portfolio status with very limited data sets, I direct them to this great site from DecisionEdge, a third party reporting tool vendor.  (Although for pure timeline views, I often recommend the Chronicle Graphics product.)

It looks like the fine folks at Decision Edge have removed their gallery of reports from their Website, but as I recall they used to have an entire selection of milestone-based reports available on their site, things like the Cumulative Milestone Report above or the Baseline Execution Index report.  If you’re looking for a selection of reports, you could definitely do worse than examining their add-in.

The Cumulative Milestone Report has been percolating in the back of my brain for a while, and I decided that what with my ongoing interest in BI and my newly arrived copy of SQL Queries for Mere Mortals, I might see if I couldn’t generate something pretty similar.

So I did.  And that’s what this post is about.  Today, I’ll talk a little about the SQL query…and in tomorrow’s post, I’ll talk about how to configure the report in Excel.

The Value Proposition

Why should you consider implementing a report like this?  Good question, I am glad you asked.  When I initially developed it at a portfolio level, I figured that this sort of report would be a good measure of how effective the enterprise PMO is at improving project performance over time.  As I mentioned above, the nice thing is that this report only requires a couple of milestones and a baseline to function.

However, the single project version of the same report is essentially the same (as I’ll discuss tomorrow).  So if you’re looking for an easy graphical indication of the state of your project, then this report would also work – again because it has a very low data entry overhead.

The Query

First off….the query.  Feel free to copy this and wait till tomorrow for the Excel configuration.  Read further to understand the mechanics of how it works in case you would like to tweak it.

SELECT     Set1.TaskIsMilestone, dbo.MSP_EpmProject_UserView.ProjectName, Set1.TaskFinishDate AS PlannedFinish, NULL AS BaselineFinish,
                      TaskActualFinishDate AS ActualFinish, DATEADD(day, 0, DATEDIFF(day, 0, Set1.TaskFinishDate + 1 – DATEPART (dd,Set1.TaskFinishDate))) AS TotalDate
FROM         dbo.MSP_EpmTask_UserView AS Set1 LEFT OUTER JOIN
                      dbo.MSP_EpmProject_UserView ON Set1.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE     (TaskIsMilestone = 1) AND (TaskBaseline0FinishDate <> 0)
SELECT     Set2.TaskIsMilestone, dbo.MSP_EpmProject_UserView.ProjectName, NULL AS PlannedFinish,  Set2.TaskBaseline0FinishDate AS BaselineFinish, NULL
                      AS ActualFinish, DATEADD(day, 0, DATEDIFF(day, 0, Set2.TaskBaseline0FinishDate + 1 – DATEPART(dd,
                      Set2.TaskBaseline0FinishDate))) AS TotalDate
FROM         dbo.MSP_EpmTask_UserView AS Set2 LEFT OUTER JOIN
                      dbo.MSP_EpmProject_UserView ON Set2.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE     (TaskIsMilestone = 1) AND (TaskBaseline0FinishDate <> 0)
Order By TotalDate

Drop this into an ODC file to get the the data set we will require.  For more instructions on doing that, please see this post.  Since the query is based off of built-in fields, it should work in pretty much any Microsoft Project Server environment without requiring any major adjustments.

Since I expect few organizations to be able to use this without some tweaking, let me walk through how I developed it and how I developed the logic.  I am sure the query itself could be improved, and welcome any suggestions on how to do so.

Field Selection

After some playing around, it seemed that I needed the following fields:

  • Task is Milestone – to filter on milestones only (although there’s no real reason that’s required.  You could use the same approach to look at every task in your portfolio if you wanted).
  • Project Name – the default report shows every project in the portfolio.  This field can be used to filter on a specific project or set of projects.
  • TaskFinishDate – The planned/estimated/scheduled finish date for the milestone.
  • TaskActualFinishDate – The actual finish date for the milestone.
  • TaskBaseline0FinishDate – The baseline finish date for the milestone.

Date Modification

The first thing I found when I developed my initial query and dropped it into Excel was that I needed to standardize the dates that would appear in the rows of data.  For instance, if I had a row indicating 8/15/2011 and a row indicating 8/17/2011, then those two rows wouldn’t easily lend themselves to totaling all of the milestones in the month of August.


If you’re familiar with PivotTables, at this point, you might point out that I could have used the Excel Grouping functionality.  True, that sort of works, but what I found is that the running total fields don’t work with Grouping, i.e. the running total or cumulative fields all end up resetting at the beginning of each grouped period.  Below is what an early attempt looked like when using the native Grouping functionality.


Hence, after doing some sleuthing on the Web, I decided to create a field (TotalDates) containing all of the Baseline and Finish dates converted to the first of the month so I wouldn’t have to worry about grouping.  So I converted both 8/15/2011 and 8/17/2011 to 8/1/2011.  I also had to strip out the time element of the date as I found some milestones were scheduled to start at 5PM and some at 8PM, and these wouldn’t get aggregated properly within Excel.

In the query, you’ll see a number of date fields associated with this formula:

DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_EpmTask_UserView.TaskFinishDate + 1 – DATEPART(dd, dbo.MSP_EpmTask_UserView.TaskFinishDate))) AS TaskFinishDate

…which is my inelegant way of stripping out all of the extraneous details and converting everything to begin at 12:01AM on the first of each month.

Combining Two Data Sets

The second challenge I found was that I had two data sets that needed to be combined.  To create the an accurate Pivot Table in Excel, I would have to combine a list of all of the Finish dates with a list of all of the Baseline Finish dates.  Once I had that complete list, I could map the various summary data to each row.

My first attempt was to create two Pivot Tables in Excel, one with Baseline data, and one with a combination of Planned and Actual dates (as Planned = Actual once Actual has been populated).  That looked like this:


I was able to generate the chart the way I liked it, but it seemed a bit kludgey  – whenever I wanted to filter data I would have to filter in both tables at the same time.  That could get annoying.

After playing around a bit more, I ended up using the UNION ALL function in SQL which essentially combines two different data sets into a larger table.  This allowed me to create a set of data with the Planned date values and to create another set using the Baseline date values, then add both sets into a single table.

I set the Baseline fields to null for the Planned data set, and the Planned & Active fields to null in the Baseline data set, as depicted in the table below.

  Set 1 (Planned/Actual) Set 2
Actual Finish Actual Finish Null
Finish Finish Null
Baseline Finish Null Baseline Finish
TotalDate Finish Baseline Finish

Here’s what the data looked like in SQL.


Filtering on Baselined Milestones

I should also point out that the query is filtered on baselined milestones only.  I did this because 1) the Contoso image doesn’t have many baselined projects, and I figured it wouldn’t be a compelling report if the unbaselined milestones far outnumbered the baselined milestones and 2) because I had some loose assumption that an organization would care at a high level about milestones that were baselined in the beginning of the project, but may not care about extra milestones added subsequently by the project manager to enhance their own control efforts.

If you wished, you could filter on your own flag field, like say “Major Milestones.”

…and I think that’s it for the query.  Tomorrow, the Excel component.

Developing a Cumulative Milestone Report (Part I)