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 »

Wanna See Some Visio MVPs?

Last week, Microsoft invited all worldwide MVPs to the annual MVP shindig in Seattle.  So, I thought it may be interesting for some to see what a Visio MVP looks like!

3Amigos2

Left to right – me (MVP UK), Senaj Lelic (MVP Germany) and John Marshall (MVP Canada) – the oldest and longest serving Visio MVP!

2Amigos

David Salaguinto (Microsoft), Dave Edson (MVP USA – currently), Tim Davenport (Microsoft), Graham Wideman (MVP USA/Canada), and Dan Albertson (Microsoft).

3Amigos

Senaj Lelic (again), Al Edlund (MVP USA) and Chris Roth (MVP USA/Austria/Germany/…)

We would like to thank Microsoft Visio product team for two very enjoyable days on campus (trying to beat the Outlook guys to the food), and I’m sure they really do appreciate our feedback!

Posted in Visio. 2 Comments »

Setting Visio Shape Cell Values By Connections

A recent newsgroup poster asked me to explain how to set the line color and weight of a connector according to the shapes it is connected to.  So, in this article, I have tried to explain one method of achieving this with minimal external coding.

In this example, I have created a rectangle shape that has a single Shape Data row, MyData, which has a fixed list of values, A;B;C.

The connector shape has been modified to trigger an event whenever a connection is made or unmade.  If the user successfully connects two rectangles with the same MyData value, then the line (weight and color) of the connector shape is amended to match the rectangle shapes.

image
Read the rest of this entry »

Posted in Visio. 4 Comments »

UK User Groups IT Pro Community Event

In my quest to promote Visio 2007 Professional as the visual information system of choice, I am presenting at the IT Pro Community event for the next two days, so I have posted my slides here. (Day 1 has little code, but Day 2 has lots of VBA code examples.):

http://www.ukusergroups.co.uk/index.html

Day 1 – Visualizing Information with Microsoft Visio 2007 slides:

http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/Visio%202007%20visualising%20data.pptx

Visio is a unique data visualization application, and the 2007 edition adds three new important data features that provide brand new data links, data graphics and pivot diagrams. These capabilities, along with the existing and often under-exploited tools, firmly position Visio as the foremost practical, easy to use visual information system that can be applied to a multitude of business scenarios. Microsoft Visio has been able to link ODBC data sources for many years via the Database Wizard, but this has limitations such as only a single data link per shape, and no support for stored procedures. Microsoft Visio 2007 overcomes both of these with the new Link Data programmable API, making it simple for end users and developers to create data rich diagrams that can be refreshed simply. In addition, the new Data Graphic features enable you to enhance linked shapes with icons, data bars, text callouts and color by value. The new Pivot Diagrams can be linked to data sources, including SQL Server Analysis Services, to provide interactive drill-downs of data diagrams, allowing the user to visualise, analyse, and present aggregate information in a new appealing manner. I’ll demonstrate the new features…and show how these data links can be made.

Day 2 – Visualizing Information the Smart (Diagram) Way slides:

http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/Visualising%20info%20the%20smart%20diagram%20way.pptx

Visio is a smart drawing tool, but it is even smarter when linked to corporate data, systems or processes, enabling it to be automatically refreshed to visualize the latest facts and figures. Data-linked Visio diagrams can be used for visualization of complex information; analysis of statistics; and communication to a broader audience with the Visio Viewer or as web pages.
This session will demonstrate linking Visio shapes to data from a variety of sources, including SQL Server stored procedures and XML files; how to drill-down into information using PivotDiagrams; and how to display data values with Data Graphics (and how to create your own). It will also include methods to automatically connect shapes together from data to create an Organization Chart without the Org Chart Wizard; and how to create legends for data graphic items.

UK Counties and Boroughs Map Shapes for Visio

My friend and colleague, Chris Roth, has made lots of maps available in Visio, his latest being Germany ( http://www.visguy.com/2008/03/01/map-of-germany/ ).  Well, we Brits hate being outdone by a German-American, so I have decided to make my UK Map shapes for Visio publicly available at  http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/UK%20Geography%20Structure.vsd .

I created the UK map several years ago by converting from PDF to SVG to Visio, and each of the counties and boroughs have a single rectangular Master, Admin Area.  I used code to replace the rectangle geometry with the actual shape geometry.  This means that, although they are all different, they actually have the same Master, thus making it easier to propagate any changes, such as Shape Data rows.

image
Read the rest of this entry »

Posted in Visio. 2 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