Loading...
Quick Links

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:

projectvisio_1

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:

projectvisio_2

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:

projectvisio_3

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:

projectvisio_4

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

projectvisio_5

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:

projectvisio_6

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

projectvisio_7

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

projectvisio_8

Click OK and then OK again to see the result:

projectvisio_9

 

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:

projectvisio_10

Now you have your killer one-page status report:

projectvisio_11

 

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!

 

Raphael Santos
Written by Raphael Santos

Raphael Santos is a PPM enthusiast with expertise in providing Microsoft Project, Project Online, and Project Server solutions. He has worked in several projects to implement PPM tools, including projects located in Latin America (Brazil, Peru, and Argentina), in the United States, and in Africa. He is also a trainer with more than 10 years of experience teaching users how to use Project Management tools in a more productive way. In 2016, Raphael was awarded the MVP title by Microsoft in recognition of his contributions to the Project Management community. Raphael is a PPM Consultant at Sensei Project Solutions, a certified Microsoft partner specializing in project and portfolio management deployments. Sensei offers a complete set of services to help organizations succeed with their Microsoft PPM deployments. Services include full implementation and training as well as pre-configured solutions and report packs. Visit senseiprojectsolutions.com or contact info@senseiprojectsolutions.com for more information.

Share This Post
3 Comments
  1. How do you export project data to MS Access?

    Reply
  2. great presentation ! Thanks Rapheal

    Reply
  3. Hi Kim —

    Thanks for the feedback!

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Please complete this equation so we know you’re not a robot. *

Thanks for submitting your comment!
You must be logged in to comment.