This post is the fourth in a series of posts describing several methods of generating reports using MS Project on the desktop. Last time we looked at how to use filters to narrow down the data which is displayed in MS Project reports. The technique shown below displays a column of Graphical Indicators in your MS Project schedule which provides a quick "at-a-glance" view of how the tasks are tracking against the Baseline. The technique utilizes MS Project's ability to use Custom Fields, Formulas, and Graphical Indicators (optional).
The Schedule Status Indicator Explained
MS Project views can be configured to display graphical status indicators which provide a quick view of certain data. This can allow Executives, Project Managers, and Team Members to easily manage projects on an exception basis, rather than unnecessarily spending large amounts of time analyzing detailed project data.
Schedule Status graphical indicators specify the status of each task in a project schedule with respect to the current timeline.
When the Schedule Status is evaluated for each task, the item is tested based on a set of criteria:
- Is the task complete?
- Is the task overdue?
- Has the task been baselined?
- Are we forecasting the task to finish early or late in the future?
Is the task complete?
If a task has been completed, then we no longer have the ability to leverage that item to make future schedule improvements.
A completed task is indicated with the following graphical indicator:
|
|
(Complete) |
Is the task overdue?
If we've passed the scheduled finish date for a task and it is still incomplete, then the task is overdue.
An overdue task is indicated with the following graphical indicator:
|
|
(Overdue) |
Has the task been baselined?
In order to forecast whether or not tasks will finish on time, the Project Manager should set a baseline when the project plan is initially created and accepted; this baseline is used throughout the duration of the project for comparison. Without a baseline, we cannot accurately and automatically measure variances in the scheduling of tasks.
A task which does not have a saved baseline is indicated with the following graphical indicator:
|
|
(No Baseline) |
Are we forecasting the task to finish early or late in the future?
As tasks are executed, schedule variances often occur (items finish earlier or later than originally planned) which affect the finish dates of tasks in the future. If the Project Manager has set a baseline, we can then use this baseline to forecast whether tasks will finish early or late.
A task which is forecasted to finish on-time or early is indicated with the following graphical indicator:
|
|
(Green) |
A task which is forecasted to finish less than 10% late (based on the overall duration of the project) is indicated with the following graphical indicator:
|
|
(Yellow) |
A task which is forecasted to finish more than 10% late (based on the overall duration of the project) is indicated with the following graphical indicator:
|
|
(Red) |
The Schedule Status Indicator Formula
The formula, as shown below, is the basis for calculating which of the graphical indicators will be displayed in the project schedule. It is intended to be used with a custom text field, and it tests for these six conditions:
- The task is complete
- The task is overdue (finish date has passed, and task is incomplete)
- The task has no baseline
- The task is on time or early
- The task is less than or equal to 10% late (based on the project duration)
- The task is more than 10% late (based on the project duration)
Here is the formula:
Switch(
[% Complete]=100,"Complete",
(([% Complete]<100) And ([Finish]<Date())),"Overdue",
(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",
[Finish Variance]<=0,"Green",
[Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",
[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red"
)
Creating The Schedule Status Indicator
To configure the custom text field, insert a column into the current view by selecting Insert > Column, then selecting an unused custom text field from the dropdown list (in this example, we'll use Text1):
After clicking OK, the column should be displayed in the current view; reposition it if necessary by clicking and dragging the column header:
Next, we'll insert the formula into the custom text field. To modify the text field, select Tools > Customize > Fields…, select the appropriate field (Text1, in this example), then click the Formula… button in the Customize Fields dialog box.
In the Formula dialog box, enter the formula as listed above, then click the OK button:
In the Customize Fields dialog box, for Calculation for task and group summary rows, select Use Formula. You may also wish to rename the text field and give it a name such as "Schedule Status"; simply click on the Rename… button, enter a new name, then click the OK button. Click OK again to accept the changes and close the Customize Fields dialog box.
If you've entered the formula correctly, you should see a series of values in the new custom text field as shown below:
When the formula finds one of the six conditions to be true, it displays one of the following results in the custom text field:
- "Complete"
- "Overdue"
- "No BL"
- "Green"
- "Yellow"
- "Red"
You can simply display the text data as shown above, or you can display a graphical indicator for each of the six conditions. To add Graphical Indicators, return to the Customize Fields dialog box, select the new custom text field, and click on the Graphical Indicators… button.
In the Graphical Indicators dialog box, enter each of the six results and select an image for each, as shown below:
After you've configured a Graphical Indicator for each of the six conditions, click the OK button to close the Graphical Indicators dialog box, then click the OK button to close the Customize Fields dialog box.
You should now see Graphical Indicators, rather than the data results ("Red", "Green", etc.), in the new custom text field, as shown below:
