When I try to create a simple dashboard in Microsoft Project 2007 to report program status, I find it lacking. I often need to resort to Microsoft PowerPoint to develop a program or portfolio summary scorecard. Drawing tools such as PowerPoint or Visio become an administrative burden when you need to reproduce project data separately from Microsoft Project. Each week, the drawing elements need to be updated to reflect the project’s traffic light summary status of Red, Yellow, or Green in the portfolio or program.
After exploring Microsoft Project 2007’s formulas and bar styles, I developed a dashboard that displays the colors of the project status by simply changing a custom indicator field in the schedule. This tutorial walks you through how to create the dashboard (Figure A).
Step 1: Create a custom project status indicator
The first step is to customize an available Text field in Microsoft Project to display a traffic light graphical indicator. You will modify an existing Text field with three valid values: Red, Yellow, and Green.
1. Insert the Text29 field.
2. Right-click the Text29 field and select Customize Fields.
3. Click the Rename button and name the field ProjectStatus.
4. Click the Lookup button (Figure B).
5. Add the values Red, Yellow, and Green.
6. Click Close.
7. Click the Graphical Indicators button (Figure C).
8. Add three tests for the Project Status field by entering “equals” and Red, Yellow, and Green for the appropriate value. Add a red, yellow, and green Image indicator as appropriate.
9. Click OK.
By inserting the ProjectStatus field into the Gantt Chart view, you can select from the Red, Yellow, or Green drop-down values, and the appropriate traffic light will be displayed. The next step is to modify custom flag fields based on the ProjectStatus indicator.
Step 2: Create the Late, AtRisk, and OnTrack fields
1. Insert three custom flags in the Gantt Chart view. I selected Flag1, Flag2, and Flag3.
2. Right-click the Flag1 field and select Customize Field.
3. Rename the Flag1 field to “Late.“
4. Rename the Flag2 field to “AtRisk.“
5. Rename the Flag3 field to “OnTrack.“
6. Select the Flag1 field, click the Formula button, and enter the formula IIf([Text29]=’Red’,Yes,No) (Figure D).
7. Click OK.
8. Click the Flag2 field, click the Formula button, and enter the formula IIf([Text29]=’Yellow’,Yes,No).
9. Click the Flag3 field, click the Formula button, and enter the formula IIf([Text29]=’Green’,Yes,No).
10. Click OK.
By changing the ProjectStatus graphical indicator field, the Flag1, Flag2, or Flag3 columns will change from No to Yes as appropriate.
Step 3: Create Red, Yellow, and Green bar styles
The next step is to create the specific Red, Yellow, and Green Gantt chart bar styles that will align to the graphical indicator color.
You can modify the Gantt chart bar styles to suite your preferences. In this example, we will add three new bar styles to match the ProjectStatus indicator and the three flags.
1. Select Format | Bar Styles.
2. Insert three rows and name the rows Red, Yellow, and Green.
3. On the Red row, click the Color drop-down menu and change it to Red.
4. Change the Show For … Tasks field to the Flag1 field. Repeat the step for Yellow and Green and assign Flag2 and Flag3 respectively. Remember to assign the yellow and green colors as appropriate.
5. Click OK (Figure E).
By following these first three steps, you’ve created a set of customized fields that will change the color of the Gantt Chart bar, depending on the Red, Yellow, or Green status. I recommend hiding the Flag1, Flag2, and Flag3 fields.
Step 4: Enter project data
The next step is to enter the specific project data into the portfolio dashboard.
In this example, every project in your program or portfolio will be represented as a single task in the portfolio; also, the individual tasks are not integrated with a sub-project and represent only a basic scorecard. For a typical program or set of projects in the portfolio, I recommend entering the following data:
1. Project Name
2. Project Start Date
3. Project Duration (which calculates the project finish)
4. % Complete
5. Any dependencies between the projects as necessary
Step 5: Change the Project Status indicator based on status
As you prepare for your next portfolio review or program status meeting, you can use this program dashboard to quickly communicate each project’s status in the portfolio. You can also add various Microsoft Project data fields to the Gantt Chart table or customize the Gantt Chart further. To change the color, select the appropriate status from the ProjectStatus field, and the Gantt chart bar will change color.
The main benefit is creating a scorecard that uses Microsoft Project data instead of drawing a separate Gantt chart in PowerPoint or another tool. The dashboard can also be extended to assess new portfolio opportunities or identify specific projects that are funded, not started, or cancelled. The formatting features in Microsoft Project are flexible to customization if you can write a few simple formulas.
If you are familiar with Microsoft Project’s formulas, you can create additional objective formulas that will calculate the status rather than rely on purely subjective interpretation.
This tutorial also requires that you enter the basic project data into a Microsoft Project file that tracks the summarize status. Future applications of bar styles and formulas can be applied to the actual project schedules that are integrated and rolled up into a summary-level plan.