bVisual

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

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!

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

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

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!

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.

The diagram below attempts to explain the relationships between the cells in the rectangle, connector and page.

The red arrows indicate the values updated by the VBA code; the green arrows indicate the values referenced from the page shapesheet, and the blue arrows indicate the values referenced within the same shapesheet.

Page

I modified the page shapesheet to have three new User-defined rows in order to have a centralised list of possible data values, and the corresponding line weights and colors for each value.  This is done with a simple semi-colon separated list, which could have been updated from an external data source.

User.MyDataList="A;B;C"

User.MyLineWeights="1pt;2pt;3pt"

User.MyLineColors="RGB(255,0,0);RGB(0,255,0);RGB(0,0,255)"

In fact, these page cells could be inserted into the Master page of both the Rectangle and Connector shape (as in the sample diagram available below) and the act of dropping either master onto a new page will have the effect of duplicating these cells in the page shapesheet.

Rectangle

Then I modified a rectangle to have a new Shape Data row (Prop.MyData), where the format cell referenced the page User.MyDataList cell.

Prop.MyData.Format=ThePage!User.MyDataList

The line weight and color of the rectangle is changed with the following formulae:

LineWeight=GUARD(INDEX(LOOKUP(Prop.MyData,Prop.MyData.Format),ThePage!User.MyLineWeights))

LineColor=GUARD(INDEX(LOOKUP(Prop.MyData,Prop.MyData.Format),ThePage!User.MyLineColors))

Dynamic Connector

Now, in order to get the connector shape to fire an event whenever the connector shape is connected or disconnected, it is necessary to call a bit of code, ConnectIT, whenever the value in the BegTrigger or EndTrigger changes.  These cells are automatically updated whenever a connect or disconnect is done.

User.ConnectITTrigger=DEPENDSON(BegTrigger,EndTrigger)+CALLTHIS("ConnectIT","")

User.BegIdx=0

User.EndIdx=0

I amended the LineWeight and LineColor formulae to respond to values in the User.BegIdx and User.EndIdx cells.

LineWeight=THEMEGUARD(IF(AND(User.BegIdx=User.EndIdx,User.BegIdx>0,ISERRVALUE(BeginX)=FALSE,ISERRVALUE(EndX)=FALSE),INDEX(User.BegIdx,ThePage!User.MyLineWeights),IF(CELLISTHEMED(FALSE),THEME("ConnectorWeight"),SETATREFEXPR(THEME("ConnectorWeight")))))

LineColor=THEMEGUARD(IF(AND(User.BegIdx=User.EndIdx,User.BegIdx>0,ISERRVALUE(BeginX)=FALSE,ISERRVALUE(EndX)=FALSE),INDEX(User.BegIdx,ThePage!User.MyLineColors),IF(CELLISTHEMED(FALSE),SETATREFEXPR(THEME("ConnectorColor")),SETATREFEXPR(0))))

The bold characters are those bits that I added into the existing LineWeight and LineColor formulae, in order to check that the index of the values of the rectangle shapes at either end match each other.  If they do, then the line is changed too.

The ISERRVALUE(BeginX)=FALSE and ISERRVALUE(EndX)=FALSE part trap the changes due to either rectangle from being deleted.

VBA Code

Whenever the code is called, it clears the values in the User.BegIdx and User.EndIdx cells, then sets a formula to return the index of the value in the connected rectangle shapes, if they exist.

The following subroutine was added to a new module in the VBA project of the document:

Public Sub ConnectIT(ByVal shp As Visio.Shape)

If Visio.Application.IsUndoingOrRedoing Then
    Exit Sub
End If
Dim cnx As Visio.Connect
shp.Cells("User.BegIdx").Formula = "0"
shp.Cells("User.EndIdx").Formula = "0"
For Each cnx In shp.Connects
    If cnx.ToSheet.CellExists("Prop.MyData", Visio.visExistsAnywhere) Then
        If cnx.FromCell.Name = "BeginX" Then
            shp.Cells("User.BegIdx").Formula = _
                "=LOOKUP(Sheet." & cnx.ToSheet.ID & _
                "!Prop.MyData,ThePage!User.MyDataList)"
        ElseIf cnx.FromCell.Name = "EndX" Then
            shp.Cells("User.EndIdx").Formula = _
                "=LOOKUP(Sheet." & cnx.ToSheet.ID & _
                "!Prop.MyData,ThePage!User.MyDataList)"
        End If
    End If
Next

End Sub

Consequently, the connector shape responds to connections, disconnections and data changes in the connected rectangle shapes.

You can download the sample drawing here: http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/ConnectTrigger.vsd or http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/ConnectTrigger.zip

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.

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.

The download is a multi-page document of over 3mb, so be warned, and it also includes a chart of the breakdown of the UK Administrative Areas.

The Admin Area (and County) shapes are linked to the Excel spreadsheet (http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/UK%20Counties%20and%20Unitary%20Authorities.xlsx), UK Counties and Unitary Authorities.xlsx, which contains columns of data about each area, and provides an hyperlink  Notice the formula to concatenate two strings):

=HYPERLINK("http://en.wikipedia.org/wiki/" & SUBSTITUTE(C2," ","_"))

I found out that the Visio 2007 Professional LinkData to Shapes function automatically  thinks that values with "xxx:" at the start are hyperlinks, so I had to uncheck the Hyperlink setting for the Information column.  (Interestingly, "Code:xxxx" opens op Lutz Roeder's Reflector app on my PC!)

After making this change, I was able to Automatically Link the data to the shapes where Name = Name, and end up with a hyperlink that points to the Wikipedia for the region.

NB.  I was going to use the Visio 2007 Professional Color By Value to automatically fill the regions, but I discovered that there is currently a 100 discrete value limit...so, I have used the Space Plan Color By Value legends - that does not have the limit!  Maybe next version...

Enjoy!

Microsoft Office Visio 2007 Professional provides the ability to color shapes by different values in selected cells.  This is great, but just how many color variations can your eyes detect?  If you have lots of different values, then the color by value automatically produced colors can look too similar to be able to distinguish them apart. In this article, I propose a method of automatically assigning different fill patterns to similar colors so that they can be visually separated.

First, we must try to understand how the color by value works. In the following example based on a large number of uniquely named departments, I wish to be able to distinguish the spaces assigned to each one on a floor plan.  I have just shown the color assignments for the first 20 departments (automatically sorted alphabetically) created with a Color By Value Data Graphic item for the Department shape data values.

Notice how the color assignments have started again from the Red for Dept 23?  So, we have reached the limit of automatically assigned colors.  Of course, we could decide to blend our own colors using the color picker, and there are a possible 16,777,216 different color combinations using the RGB or HSL notation, but just how many variations can the human eye perceive?

Now, the Data Graphic Color By Value formulae are added to an automatically created User.visDGCBVFill cell in the ShapeSheet of the Data Graphic master, and, therefore to all shapes that use it.

In fact, if the shape is a group, then an almost identical formula is added to the sub shapes too, except that they refer to the cell value in the group shape.  Thus Prop.Department would become Sheet.5!Prop.Department (or whatever the sheet ID is).

N.B.  If you intend to use Data Graphics with instances of a Master, then it is probably worthwhile making it into a group at the start, since any other Data Graphic items (Text Callouts, Data Bars and Icon Sets) will automatically convert the instances into groups. 

The color of the background fill (FillBkgnd) is read from the result of User.visDGCBVFill and the foreground fill (FillForegnd) is a TINT of the back ground fill.

I should explain (or remind you if you knew this already) that the foreground and background fill colors are only both used  when the fill pattern is not 1 (Solid).  In fact, the presentation of foreground and background is reversed in the user interface when a fill pattern is not Solid.

This is the standard fill patterns, starting with no fill, then white (RGB(255,255,255)) solid fill foreground then applying each standard fill pattern with a blue (RGB(31, 71, 125)) pattern color.  Notice how the FillForegnd and FillBkgnd cell values switch above a FillPattern of 1.

Now, the Data Graphic Color By Value uses Solid colors, therefore it is necessary to look at the reverse of the above to get a better idea of how these fill patterns would appear in a shape.

Now, not all shapes will be rectangular in appearance, so some patterns work better than others.

So, here are 40 different departments using the default color by value colors...

As you can see, it is a little difficult to distinguish some of the colors because they are exactly the same!

In the screenshot below, I have applied a VBA function, UpdateCBVFillPatterns, which has additionally cycled through a list of fill pattern numbers (in this case "13;14;15;16;17;18" ). 

You can now visually distinguish different departments!

I have included the whole VBA code below for you to try.  Please note that this currently expects you to select one shape that uses Color By Value before running the it, and it only updates the active page, but it could be extended to update the whole document quite easily.

In addition, I am looking into extending the legends created by our own DataLegends product (http://www.bvisual.net/Products/DataLegends.aspx ) to also show the fill patterns. 

Public Sub UpdateCBVFillPatterns()
Dim shp As Visio.Shape
    If Visio.ActiveWindow.Selection.Count = 0 Then
        MsgBox "There is no shape selected!"
        Exit Sub
    Else
        Set shp = Visio.ActiveWindow.Selection.PrimaryItem
    End If
Dim mstDG As Visio.Master
    If shp.DataGraphic Is Nothing Then
        MsgBox "The selected shape does not have a data graphic!"
        Exit Sub
    Else
        Set mstDG = shp.DataGraphic
    End If
Dim gi As Visio.GraphicItem
    For Each gi In mstDG.GraphicItems
        If gi.Type = visTypeColorByValue Then
            Exit For
        End If
    Next gi
    If gi.Type <> visTypeColorByValue Then
        MsgBox "The selected shape does not have a Color by Value graphic item!"
        Exit Sub
    End If
'Enter the list of standard patterns to use
Dim patternList As String
    patternList = "13;14;15;16;17;18"

Dim bkgndColor As String
    bkgndColor = "RGB(255,255,255)"
Dim mst As Visio.Master
Dim shpMst As Visio.Shape

'The Shape could have a master, so best to update it
If Not shp.Master Is Nothing Then
    Set mst = shp.Master.Open
    addTriggerCell mst.Shapes(1), patternList, bkgndColor
    'Could be applied to a sub-shape
    For Each shpMst In mst.Shapes(1).Shapes
        addTriggerCell shpMst, patternList, bkgndColor
    Next
    mst.Close
End If

Dim sel As Visio.Selection
Dim shpSel As Visio.Shape
Dim shpSub As Visio.Shape
    'Select all of the shapes in the active page that use this Data Graphic
    Set sel = Visio.ActivePage.CreateSelection(visSelTypeByDataGraphic, 0, mstDG)
    For Each shpSel In sel
        addTriggerCell shpSel, patternList, bkgndColor
        'Could be applied to a sub-shape
        For Each shpSub In shpSel.Shapes
            addTriggerCell shpSub, patternList, bkgndColor
        Next
    Next
End Sub

Private Sub addTriggerCell(ByVal shp As Visio.Shape, _
    ByVal patternList As String, ByVal bkgndColor As String)
    'Check that visDGCBVFill cell exists
    If shp.CellExists("User.visDGCBVFill", Visio.VisExistsFlags.visExistsLocally) = 0 Then
        Exit Sub
    End If
'Get the color formula
Dim clrFormula As String
    clrFormula = shp.Cells("User.visDGCBVFill").FormulaU

'Collect the matching values in order
Dim aryFormula() As String
    aryFormula = Split(clrFormula, "STRSAME(")

If UBound(aryFormula) < 1 Then
    Exit Sub
End If

Dim ipart As Integer
Dim aryValues() As String
    For ipart = 1 To UBound(aryFormula)
        ReDim Preserve aryValues(ipart - 1)
        aryValues(ipart - 1) = Split(aryFormula(ipart), ",")(0)
    Next ipart

'Get the cell reference
Dim cellRef As String
    cellRef = Split(aryFormula(1), ",")(1)
'Create the index of the value from the lookup list
Dim idxPart As String
    idxPart = "LOOKUP(" & cellRef & ",""" & Replace(Join(aryValues, ";"), """", "") & """)"
Dim aryPatterns() As String
    aryPatterns() = Split(patternList, ";")
'Build the trigger formula
Dim tFormula As String
    tFormula = "DEPENDSON(User.visDGCBVFill)"
    tFormula = tFormula & "+SETF(GetRef(FillPattern),INDEX(MODULUS("
    tFormula = tFormula & idxPart & "," & CStr(UBound(aryPatterns) + 1)
    tFormula = tFormula & "),""" & patternList & """))"
    tFormula = tFormula & "+IF(INDEX(MODULUS("
    tFormula = tFormula & idxPart & "," & CStr(UBound(aryPatterns) + 1)
    tFormula = tFormula & "),""" & patternList & """)"
    tFormula = tFormula & " < 2,SETF(GetRef(FillBkgnd)," & bkgndColor & "),"
    tFormula = tFormula & "SETF(GetRef(FillForegnd)," & bkgndColor & "))"
Dim irow As Integer
    If shp.CellExistsU("User.PatternTrigger", Visio.VisExistsFlags.visExistsAnywhere) = 0 Then
        irow = shp.AddNamedRow(Visio.visSectionUser, "PatternTrigger", 0)
    Else
        shp.Cells("User.PatternTrigger").FormulaU = "="
    End If

    'Apply the formula
    shp.Cells("User.PatternTrigger").FormulaU = "=" & tFormula

End Sub

Those of you who are attending the Visio Conference in Redmond this week will know that I am presenting in the developer track.  I am demonstrating some automation examples for building organization charts from SQL Server AdventureWorks database (slightly enhanced) in Visio 2007 Professional, without using the Org Chart Wizard.

This means that you have far more flexibility with the diagram layout, and it demonstrates Link Data to Shapes, Data Graphics and Pivot Diagrams.

The example stencil which includes all of the VBA samples used is attached to this post, along with the PowerPoint slideshow, and the SQL Server scripts to enhance the AdventureWorks database.

http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/MSVisioConference2008_DJP.vss

http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/VisioConf2008_DJP.pptx

http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/VisioConf2008DBScripts.zip