I have recently been asked if it is possible to update the % Complete data diagrams created with the WBS Modeler Visio 2007 Add-in (http://www.microsoft.com/downloads/details.aspx?FamilyID=34c28a49-e14c-4a7d-8d49-90061fe08ab4&DisplayLang=en). Well, it is always possible with custom code, but it is intended that the WBS Modeler diagrams can be refreshed using the Link Data to Shapes feature in Visio 2007 Professional. I know this because I wrote WBS Modeler, and in this article, I will tell you how.
Firstly, I will use the Commercial Construction project that is installed with the Visio SDK for this example, which I show an extract from below.
Then I created a new WBS Modeler diagram based on the above project.
I simply imported the whole project using the WBS Modeler / Import from Microsoft Office Project menu action.
The Element shapes are color coded according to their Outline Level by default, and they are labeled with their Outline Number and a truncated version of the Element Name shape data. Notice that the Shape Data window shows more of the Project data fields than is displayed on the shape.
NB If you do see all of these Shape Data rows, then click on the page and ensure that the page Hide extra Project properties is set to False in the Shape Data window.
In this particular sample project, there are no costs, nor are there any non-zero % Complete values, so I’ll change some of these in the original project, then update the Visio diagram.
Therefore, I revealed the Unique ID, % Complete and Cost fields in Project.
I then exported the Project tasks to Microsoft Excel using File / SaveAs and Selected Data in the Project Export Wizard.
You then need to select the Unique ID and any other fields that you want to export.
You can optionally save the Map for future use, but you should end up with an Excel spreadsheet that contains the data that you requested.
Then, in Visio 2007 Professional, you can click Data / Link Data to Shapes and select the Excel workbook that you just created. There should be just one region, Task_Table1$, and you should ensure that the Unique_ID column is checked.
When you click Finish, the Excel data is copied into the Visio document as External Data. You must now ensure that the column labels match those on the Element shapes by opening the Column Settings dialog from the right-mouse menu of the External Data window. So, Unique_ID becomes Unique ID and Percent_Complete becomes % Complete. This will ensure that the data is copied into existing Shape Data rows rather than creating new ones.
In addition, I changed the Data Type for the Cost field to String because my original Element shape is expecting a string.
You can then choose Automatically Link from the right-mouse menu of the External Data window, and use the All shapes on this page option. Finally, you must ensure that the Data Column Unique ID equals the Shape Field Unique ID.
When the links have been created, you will probably think that something has gone wrong because everything has turned black.
However, this is normal because Visio has automatically assigned a Data Graphic to all the shapes, so all you need to do is edit it. In this example, I simply deleted the two text fields that Visio had automatically created, and then added a new Data Bars for the % Complete and Cost fields, and Color By Value for the Parent Element.
I entered the Custom Formula={% Complete}*100 so that the numbers would be between 0 and 100. You can enter these formulae by selecting More Fields at the bottom of the Data Fields drop-down list on the New Data Bar dialog.
I also edited the Label text and selected the position to be Center/Bottom Edge.
Although I set the Cost property as String for the import, Visio is still able to treat the contents as a number for the Data Graphics, so I thought a Thermometer Data Bar would be interesting. Note that I set the Maximum Value to more than the known data values. (See my Visio Conference blog for code on how to automatically update maxima and minima values).
Finally, I decided to use Color By Value on the Parent Element property rather than on the Outline Level one … just to make the diagram more interesting.
When all of this is applied, then you will hopefully see how much more understandable the data is when Link Data to Shapes and Data Graphics are combined in Visio 2007 Professional.
So,the overall effect is a linked WBS Modeler diagram that can be refreshed whenever the data is published into the Excel workbook from Project.
Of course, any Visio diagram that utilises Data Graphics is much enhanced by a legends for the linked recordsets, Color By Value and Icon Sets. However, this is not available out-of-the-box, so you will have to trial (then hopefully buy) my own DataLegends add-in (http://www.bvisual.net/Products/DataLegends.aspx)
The files above are available for download at http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/CommercialConstruction.zip
