Project Management

Create a program dashboard in Microsoft Project 2007

Learn how to create a Microsoft Project 2007 dashboard that displays the colors of the project status by changing a custom indicator field in the schedule.

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). 


Figure A
ProgramDashboard_Makar_080813_FigA.PNG

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). 


Figure B 


ProgramDashboard_Makar_080813_FigB.PNG


5. Add the values Red, Yellow, and Green.

6. Click Close.

7. Click the Graphical Indicators button (Figure C). 


Figure C

ProgramDashboard_Makar_080813_FigC.PNG


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). 


Figure D

ProgramDashboard_Makar_080813_FigD.PNG


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). 


Figure E

ProgramDashboard_Makar_080813_FigE.PNG


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.

Objective extensions

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.

About

Dr. Andrew Makar is an IT program manager and is the author of How To Use Microsoft Project and Project Management Interview Questions Made Easy. For more project management advice visit http://www.tacticalprojectmanagement.com.

18 comments
avinintechrepublic
avinintechrepublic

This page looks hacked ..following on from Figure C , all the images are out of topic. Could someone look into this and pls update ? Many thanks

Jason Mier
Jason Mier

The dashboard can also be extended to assessing new portfolio opportunities or identifying specific projects that are funded, not started or cancelled. hire a programmers

MargaretStudent
MargaretStudent

Just came across this and found it very useful. FYI, I found a better solution for step 3 than deleting the Task Bar so the colors would show up and keep the default blue bar. I added "No Status" under ProjectStatus and made it Flag4. I changed the regular Task Bar to be labeled "No Status" and added Flag 4 to the "Show For...". Then I could keep the default 'blue bar' for something where a status had not been assigned yet.

Meadowsong
Meadowsong

Much more productive than the tedium of maintaining the same content in Powerpoint. Thanks.

Withoutheart
Withoutheart

Following all the steps, when I select an indicator in ProjectStatus field, the bar's color in the rignt pane doesn't change. Instead, the proccess line's color changes.

Funker
Funker

Can you be more specific? The screenshots are missing but the instructions appear complete. I just ran through the tutorial in Project 2010 and had no issue.

ArchGov
ArchGov

uh ... some steps are apparently missing between steps 1.6, 1.7 and 1.8. Any recommendations?

jriedel99
jriedel99

Wish I could do this in 2003... great feature!

tlmaier
tlmaier

I followed the instructions completely, but the bar colors did not change. Was there a step that is intuitively obvious to you that I missed?

Withoutheart
Withoutheart

In the end of Step 3, if not only the 3 bar style in the list, you have to select Task in Name colume and reset the Show For ? Tasks to an unused flag. (In this sample, all the other things except Red, Yellow and Green are deleted, I think.) Other wise, the bar will not change it's default color.

ArchGov
ArchGov

My inexperience and the missing screenshots probably caused the issue. I executed Steps 2.1-2.10 (i.e., I created the Late, AtRisk and OnTrack fields) then went back and executed Steps 1.1-1.9 and they worked. Are the 3 new fields perhaps required BEFORE Steps 1.7-1.9 will work?

rohilsave
rohilsave

Hi jriedel99, It is possible to do step 1 & 2 in Project 2003. But it is not as flexible as 2007 in the Gantt Chart department. Thanks, Rohil

prakash.mistry
prakash.mistry

I have the exact same problem, the bars don't change colors according to the project status change.

ChrisEvans
ChrisEvans

I had an issue with this. In my case I believe it was because I was customising the overall settings rather than the ones specific to my Gantt Chart. If you want to send me your email address I will happily send you an example that I have set up as a template for future use.

amakar
amakar

Hi - The idea is to create the graphical indicator flag first. You're customizing 1 field to have 3 valid drop down values. Based on these values, you'll display the graphical red, yellow or green icon. Once this is created, you've simply build a graphical indicator. The next step to set the 1 of 3 custom flag fields to Yes so the Gantt Chart can be formatted dynamically. Step 2: Customize these 3 fields to display Yes when the graphical indicator field is set to Yes. The formulate in these flag fields evaluates the graphical indicator field (text29) and if it is set to Yes, the appropriate flag will be set to Yes. MS Project's Bar Styles relies on a flag field to determine if a specific color should be set. Step 3: Format the bar styles to change color based on the flag fields you created in step 2. Email me at andy@tacticalprojectmanagement.com if you have additional questions. I'm happy to assist! Andy

TracyMcb
TracyMcb

If your bar colors aren't changing, try inserting the new bar styles at the end of the Bar Style table. The bars are processed in the order they are listed, and the last bar that meets the selection criteria is the one that is used.

Carl Browning
Carl Browning

Step 3 of this guide omits that after inserting the 3 rows for Red, Yellow & Green, you must remove all remaining rows for the bar colours to work. Hope that helps