Linking Visio WBS Modeler Diagrams to Project

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.

image

Then I created a new WBS Modeler diagram based on the above project.

image

I simply imported the whole project using the WBS Modeler / Import from Microsoft Office Project menu action.

image

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.

image

I then exported the Project tasks to Microsoft Excel using File / SaveAs and Selected Data in the Project Export Wizard.

image

You then need to select the Unique ID and any other fields that you want to export.

image

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.

image

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.

image

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.

image

In addition, I changed the Data Type for the Cost field to String because my original Element shape is expecting a string.

image

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.

image

When the links have been created, you will probably think that something has gone wrong because everything has turned black.

image

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.

image

I also edited the Label text and selected the position to be Center/Bottom Edge.

image

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

image

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.

image

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.

image

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.

image

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

Posted in Visio. 29 Comments »
JackBinnall

O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

davecra.wordpress.com/

Azure Solutions for Office 365, and more...

Rob Fahrni

I AM FAHRNI

johnvisiomvp

Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Smart graphics for visual people