Create a Killer One-Page Status Report with Project & Visio

Note: the images in this article are based on a Portuguese installation of the software used. However, they will be translated to the English language in the text.

One question I always get when I visit my customers is: “Is there a way to create a link between Project and Visio?”  In most cases, you have to use code or additional add-ins. However, I wanted to explore a different way.

I started trying to think of a way to create a one-page status report, containing only summary-level tasks, which should show the progress of each phase of the project as well as if the schedule has any variance. And I wanted to create it in Visio.

As I explained in this article, I can export data from Project to Access while maintaining the data linked. Based on this, I figured out how to make that report, which I will explain below.

Before starting

There are a couple files that will be used in this example:

• The WBS containing the summary-level tasks of the project in the .vsd format,
• The schedule in a .mpp file.

Working with the data

In order to have a database with all the information about your project,  first thing you have to do is to export the data from Project to Access,  After completing the export of the data, you will have to open the WBS that was created in Visio.

With the Visio file open, click Data > Link Data to Shapes. On the Data Selector wizard, select Microsoft Access Database and click next. Then, you will have to indicate where your Access database is located. After that, you have to specify which table of the database you want to use. Select MSP_EpmTask:

Click next. The next step allows you to make filters on the data that will be imported, granting you the ability to work only with the data you want. Click on the Select Columns button, and then select all the columns that will be used on the report:

• TaskIntUID
• Name
• WBS
• Percent Completed
• Finish Variance

Click OK. Now click on the Select Rows button, in order to apply a filter that will bring only the summary tasks of the project. To do this, on the Filter rows dialog box, click on the first dropdown and select Custom:

Define the following criteria:

Field: Is Summary
Comparison: Equals to
Compare to: True

Click OK and follow the instructions of the wizard until the import process is completed. Now you will be able to see the External Data Window, which shows all the tasks that were imported from the Access database:

Building the Report

Once you have Visio reading the data from Access, you can start building your report. Visio offers a large variety of ways for creating stunning visual Dashboards, allowing you to establish a connection between the imported data and the shapes of your diagram, as Chris Hopkins shows in this amazing video.

If the names inserted in the shapes of your diagram are the same as the imported data, you can easily click on the Automatically Link button and let Visio do the job. If they aren´t, you can drag-and-drop them from the External Data Window into each shape. In this case, click Automatically Link and create a link between the Name column of the imported data and the Title field in the shape:

Follow the steps until the links are created. You will see your WBS something like this:

Now, all you have to do is to adjust the way you want to present the information. First, select all shapes and then click on Data Graphics > Create New Data Graphics. Click on the New Item button; you will have to choose the fields you want to show on the shapes – and how they have to be displayed. Click on More fields and select the Shape Data category:

Select the Percent Completed field and click OK. Then, on the Show as option, select the Data Bar style. Change the Maximum Value option to 1 (number one):

Click OK. Click on the New Item button again, and select the Finish Variance field. On the Show as option, select the Graphical Icons style. You have to define the rules that must be applied for the icons you want to use. In this case, I will create a simple rule:

If Finish Variance is Greater than 0 > show the Red flag

If Finish Variance is Equals to or Lower than 0 > show the Green flag

Click OK and then OK again to see the result:

The only problem here is that by default, Visio always shows the values aligned to the right of the shape. To change that, select the shapes Initiation, Installation and Training. Then, click on Data Graphics > Edit Data Graphics. On the dialog box, change the horizontal position to the left in both fields. Don´t forget to select the Only to the selected shapes option:

Now you have your killer one-page status report:

Once you have your Project updated, all you have to do is to export from Project to Access and replace the old database. Then, on Visio, click on Data > Refresh All.

Hope you liked this!