Expected % Complete: An early warning indicator for project work

Last fall (October 2015), I was working with a client who wanted to provide the typical red, yellow, green "stop light" indicator for tasks that were essentially behind schedule as a quick visual for Project Managers. This then would rollup to the project level to be factored into the overall project schedule "health" calculation. There were many discussions around what "behind schedule" meant for them because there are many ways and different factors that can determine if a task is considered behind schedule as well as what threshold did the business what to consider a task for preventative or corrective action. Out of those, we decided to focus on a metric that we called "Expected % Complete". Because they would be using Percent Complete to record progress on task, this consisted of calculating the percentage complete that the task should be at a given date if all work was progressing normally. For example, if you have a task that has a duration of ten (10) days, on day five (5) the task should be expected to be 50% complete. If the task is actually only 20% complete at this halfway mark then the indicator might show a red stoplight. If the task was actually 70% complete, then the indicator would likely show as a green stoplight. We also had quite a number of discussions debating the use of baseline Start and Finish dates (Baseline Start / Baseline Finish) over what are essentially the estimated Start and Finish dates, as well as whether to use the Project Status Date or the current date. Which is right for you and your company may be different, but for purpose of this post we will be targeting the estimated Start and Finish dates as well as the current system date. You should be able to use the content in the rest of this post to modify the formulas and values for what is right for your particular situation. Let's setup the scenario. First, we are assuming that % Complete is being used to record progress against tasks. Second, we will be assuming that all tasks are duration based tasks. If you are using another task type, for example fixed units or fixed work tasks, then you will need to adjust the formula accordingly. Third, we will be using the current system date to calculate the Expected % Complete and not the Project Status Date. Lastly, I'm assuming that you already know how to create either local custom fields within your project in Microsoft Project or know how to create Enterprise Custom Fields within Project Server or Project Online. Also, I used a text custom field to hold this formula because the client wanted the percent complete formatted as a percentage strictly for visual purposes. This actually made creating the indicator field harder because I had to convert it back to a number before evaluating it against the red, yellow and green thresholds.

Now the formula:

Format(IIf(([Duration]=0),0,(IIf(([Estimated]="Yes"),0,(IIf(([Finish]<Now()),1,(ProjDateDiff([Start],Now())/[Duration])))))),"0%")

Don't get freaked out here. It's not as bad as you might think. A lot of this is testing certain conditions that might occur for a task such as whether the duration is zero like for a milestone task ([Duration]=0), the duration is only estimated ([Estimated]="Yes"), and whether the task's finish date has already passed ([Finish]<Now()). Let's break it down, okay?

First, the heart of the calculation is this: (ProjDateDiff([Start],Now())/[Duration])). Basically this says subtract the current date from the task's Start date field and divide by the duration of the task. For this we use to functions, the Now() function to represent the current date and the ProjDateDiff function to subtract the dates. For those familiar with VBA, you might be wondering why we are not using the DateDiff function. The ProjDateDiff is a special Project only function. It's special because it takes into account the calendar settings and hours per day that might be set for a particular task. Why is that important? Remember that tasks can use either the calendar that is for the overall project or a specific task calendar. Additionally, if you have resources assigned to that task (and you absolutely should) then they can each have individual calendars associated with them as well. Those will define that resources work day, hours and such. So, let's take a simple example. Let's say that you have a task that has a calendar that defines a work day as 10 hours/day and another task that has a calendar that defines a work day as 8 hours /day. Rather than having to figure out how the day unit is define for that task, and add those consideration to your formula, you can use the ProjDateDiff function to do all the heavy lifting for you. Additionally, duration can be a particularly vexing field to use in calculations because the value that comes back from referencing a duration type field is represented in minutes, not hours or days. There is also a really great constant that is available that can be used called [MinutesPerDay] that takes into account the calendar et al. Here I'm not using it because both the result of the ProjDateDiff function and the Duration are in minutes already. I don't need to convert them to anything else to get my Expected % Complete.

The second thing that might be throwing you is the IIf parts of this formula. This is known as an Immediate IF function. Basically it says, if the first part is true do this, other wise to that. It's format is like this: IIf(expr, truepart, falsepart). Because I'm testing for different conditions that might exist in the task, I have several nested IIf functions in the formula where the false part of the IIf function is another IIf function. First, I test to see if the duration is zero. If it is then the percentage is zero. Next, if the duration is still set as an estimated value, either because it still has the ? or is set to Estimated, this can do weird things to the duration value so I want to nip those in the bud. Lastly, I check to see if the Finish date is before the current date because if it is no matter how long ago this task should have been finish it should be 100%. Wrap all this in a Format function to format the result as a percentage and you are done.

So let's see this in action. Below is a project schedule with the Expected % Complete value custom field with a system date is 01/24/2016. I've filled in several percentage completes and also exposed several additional fields for you to peek at. Now all that's left is to create an indicator field based on my Expected % Complete value and the red, yellow and green thresholds.

Links: Project functions for Custom Fields: http://bit.ly/25dFPAd

email Christine: christine.flora@symnoian.com

Updating a Project field from Workflow, SharePoint Designer 2013

With Project Server 2013, the ability to easily create workflows with SharePoint Designer 2013 was introduced. This is/was great news for power users and non-developers, and it's long overdue. Even for someone who can code (like me), I love this idea because 1) it gives me the ability to easily create a pretty robust workflow without opening Visual Studio, and 2) I can create workflows straight away for Project Online without needing my own on-prem environment (even though I do actually have one). Notice that I say "pretty robust." There are instances when you're creating a workflow that will require you to break out Visual Studio to do what you need to (or hire a developer), but a large part of workflows can be done within SharePoint Designer 2013 (SPD 2013). One of the many features within SPD 2013 is the ability to have your workflow update an Enterprise Project Field directly. This is an awesome feature, but it needs some work, so let me expand on that. When a user edits an Enterprise Project Field from within PWA (Project Web App) and clicks save, what's known as a "light publish" happens. If you recall, updating a project schedule will update the draft tables (formerly its own database) and a user then needs to publish those changes--appropriately known as the publish tables--so they can be seen by everyone from within PWA. When you use the Set Project Field action within a workflow, the value is essentially stored in the draft tables. What this means is that if a user simply looks at the project from within PWA, the value doesn't look like it has been changed. Also, any views that might be keyed on this new value don't show it either. But if you edit the project, the new value shows up. Click save, and all of a sudden the value is viewable. It would be nice if this action would automatically "light publish" this change, or better yet, stuff the value in both the draft and publish tables at once. Even better still, it would be great to have an option that the workflow author can set to turn on or off the light publish feature; that way the author can decide if he or she is ready to initiate the light publish. It doesn't do any of that now, and that is a problem. Fortunately, there are REST calls you can make from within the workflow to do this, but they can be hard to understand and construct. I can sort of see the reasoning behind not having the light publish option as part of the action, since you may want to set several project fields before initiating this light publish. Not having this built in is kind of a pain, though, and not really user friendly to those non-developer types who are probably using SPD2013 to begin with.

Essentially then, after setting any project fields with this action, you will need to use the Call HTTP Web Service action in order to 1) check out the project, and 2) initiate a publish. Unfortunately, this is a full publish and not just a light publish, so if there are changes to the project schedule, those are going to be published as well. Okay, here's how to do this.

First, I like to create a variable to hold my instance reference so I can reuse it throughout my workflow. Believe me, you'll want to do this. You can call it anything. I've picked up the habit of calling it "ps" for "Project Server" from the talented, helpful, and ever-patient Biatrice "Bia" Ambrosa from the MS Project product team. It just makes sense, and I like easy. Notice that I am using an instance reference (specifically using a project variable reference) and not hard coding those values into the workflow. You can access these by clicking on the "Add or Change Lookup" button in the lower left.

Next, you'll want to create a variable to hold your HTTP Web Service call headers as well. Yes, you will be reusing these too. It is actually a dictionary, and not just a simple one-value variable, so use the Build Dictionary action.

Now you are ready to construct your first HTTP Web Service call using both the Project Server instance variable ("ps") and the "requestHeaders" (or whatever you named it) from above. Most HTTP Web Service requests that you do within a workflow are "GET" calls, meaning you are going out and getting information, like when you are reading in OData. These are POST calls, meaning they update or are sending information to the service you are calling, so be sure to change the HTTP method to POST.

You should do some sort of error checking after this step to make sure you were actually able to check out the project before continuing.

Last, construct the HTTP Web Service Call to call the Publish() REST method. Because you've already done most of the work, you have most of the call already constructed. Using the "ps" variable and the "requestHeaders" dictionary, add the Publish() call.

Now save and publish your workflow, and the Enterprise Project Field value will be updated. If your workflow is like mine, I actually had to set this field throughout the whole workflow as it progressed from stage to stage. I was able to reuse those "ps" and "requestHeader" values throughout.

The REST calls are documented in the Project 2013 SDK that you can download here.  Also check out Bia's Project Conference 2014 Workflow Deep Dive session here. I highly recommend it.

Footnote: The ability to update multiple fields at once has just been added, but you still need to publish in order for those changes be seen within PWA.