Section 1: Creating Linked Data (Visualizing Information with Microsoft Office Visio 2007)

This article is an excerpt from Visualizing Information with Microsoft Office Visio 2007 by David J. Parker from McGraw-Hill (ISBN 13: 9780071482615 copyright McGraw-Hill Companies 2007, all rights reserved) with permission from McGraw-Hill Professional. No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Next part: Section 2: Labeling Shapes from Shape Data (Visualizing Information with Microsoft Office Visio 2007)

Contents

• Overview

• Link Data Manually

• Column Settings

• Filtering Rows

• Link Data Automatically

• Merging Data from Other Sources

• Navigating to/from Linked Data

• Refreshing the Data

• Creating Data Connections in Code

• Linking XML Data

• Refreshing XML Data

• Link Data Legends

• Additional Resources

• About the Author

Overview

Linked data can be created manually, by picking existing shapes in a drawing or by associating data rows with a selected Master, or automatically when the unique identifier values exist already in shape data rows of existing shapes. In either case, the process will map fields to shape data rows or create any missing shape data rows, as required.

We are going to link some computer shapes to the Network–Computers table. If you examine the 15 fields in this table, you can see they are of a variety of data types.

Create a new Basic Network Diagram, drag a PC shape off the Computers and Monitors stencil, and then open the ShapeSheet to examine its shape data rows.

You will see 26 shape data rows, all with Type = 0 (text). Four of them are invisible in the Shape Data window or dialog, however, because they either store classification information or the BelongsTo data (used by the Space Plan add-in).

Link Data Manually

Now, select Data | Link Data to Shapes… and in the Data Selector dialog, select the sample Access database, DBSample.mdb, and the Network–Computers table. For now, do not bother to change the columns or filter the data; you should see (All Columns) and (All Data) in the Data Selector dialog. Then, click the Finish button.

The External Data window automatically opens, and it displays the rows and columns of data from the database table. The data is copied locally into the Visio file as XML, which you can now link to shapes in the diagram.

Now, ensure that you have the PC Master selected, and then drag-and-drop the top row from the External Data window into the drawing page. The PC shape will now be populated with the data from the row you just dropped, and a chain-link symbol will appear in the first column of the External Data window. If, and probably when (unless you have unchecked the Apply After Linking Data to Shapes option at the bottom of the Data Graphics panel), the Data Graphics Task Pane automatically appears, just close it down because you will examine that in the section “Labeling Shapes from Shape Data.” Notice 35 items of shape data are now visible, as some new data rows are added at the end of the shape data section.

Open the ShapeSheet for this shape and you can see what happened. In my case, you can easily spot the affected shape data rows because the LangID column has the code 2057 (English UK) rather than 1033 (English US).

The Manufacturer and Memory rows have values, but the data type for Memory is incorrect, as it has not been changed from 0 (text) to 2 (number).

There are 13 new rows, but some of them are not required (XLocation and YLocation), and some could have been mapped to existing Shape Data rows.

Column Settings

You can change the mapping of columns using the Column Settings... dialog that can be opened from the right mouse menu of the External Data window.

The column mapping is based on the Label cell in the shape data rows, so all you need to do is rename some of the column names to match those already existing in the PC shape, as the table below shows.

In addition, you can uncheck the XLocation and YLocation columns, as they are not required in this example.

Now delete the PC shape you just dragged on to the page, and redrag the first data row back on to the page (first, ensure the PC Master is selected in the stencil). This time, you should get only 27 visible shape data rows, but you should also get a warning that data was linked to hidden shape data fields in some of your shapes. This refers to the Belongs To row because it is currently set to invisible.

Suggested Column Name Changes

Old Label	New Label	Existing Data Row Name
MachineSN	Serial Number	SerialNumber
Machine Type	Product Number	ProductNumber
Machine Type code	Part Number	PartNumber
Processor	CPU	CPU
Hard Disk Space	Hard Drive Capacity	HardDriveSize
Owner	Belongs To	BelongsTo

You can now see the data from the remapped columns are going into the pre-designated shape data rows. Again, if you look at the ShapeSheet of this shape, you can see six new rows, each of which has shape data row names, which begin with _visDM_. These rows have the correct Type code (although _visDM_Screen_Size does not show 0, it will be interpreted as 0).

You could edit the ShapeSheet here to make the changes you want, such as the visibility of Belongs To and the type of Memory, but this would mean you would need to make these changes for each PC you link. So, instead, you should edit the Master in the document stencil. In a complete solution, you would probably save these amended Masters to a stencil as part of a custom template.

Delete the PC shape you just dropped on to the page again, and then navigate to the PC Master in the Drawing Explorer window. First, ensure the Match Master by Name on Drop Setting is checked in the Master Properties dialog. This ensures that the local, amended version of the PC Master will be used, even when the PC Master on the global stencil Computers and Monitors is selected.

Expand the Masters branch in the Drawing Explorer window, and select the PC Master node. Now, select Edit the Master Shape from the right mouse menu of the PC Master, and a new window will open. Select the PC shape and open the ShapeSheet. Next, change the Type cell of the Memory and HardDriveSize Shape Data rows to 2 (numeric), and the Invisible cell of the BelongsTo row to FALSE.

This is also your opportunity to alter the display order of the shape data rows by changing the values in the SortKey cells. Remember, the sort keys are alphabetic order (thus, use 01, 02, etc., rather than 1,2).

This time, when you drag-and-drop a row from the External Data window, you will get the desired number of visible shape data rows, as you should see the Belongs To shape data row.

Manual links to data can be created by dragging a data row from the External Data window on to an existing shape in the diagram. Indeed, if you have multiple shapes selected before you drag a number of rows from the External Data window, then the rows will be linked to the shapes in selection order. One note of caution: if you select fewer data rows than shapes, the same data row will be applied to multiple shapes.

The same action can be achieved by having shapes and rows selected, and then choosing the Link to Selected Shapes item from the right mouse menu of the External Data window.

You can also drag multiple rows from the External Data window with a Master selected in the stencil. You will get a cascaded display of linked data shapes, one for each row connected to an instance shape of the selected Master.

Another note of caution: do not select multiple Masters because you will get far too many shapes. In fact, you will get (number of selected rows) × (number of selected Masters). Apparently, it is supposed to work this way.

The layout of shapes created using this method will need some manual rearrangement because the cascaded layout invariably overlaps shapes.

Filtering Rows

Your data source usually has more rows than you require, so you may want to filter the data source as you create the link using the Select Rows button on the Data Selector Wizard. If you need to change the settings after you create the link, then you open the Data Selector again from the Change Data Source button on the Configure Refresh dialog. This can be opened from the right mouse menu of the External Data window.

The Filter Rows dialog presents a preview of the data and enables you to filter and/or sort the data, as required.

If you select the arrow on a column heading and then select (Custom) from the pop-up menu, you will open the Filter and Sort dialog. You can add multiple filter criteria, which can be And or Or statements.

You can sort the rows in ascending or descending order by up to three columns.

Link Data Automatically

If you have shapes in a drawing that already have shape data rows, and one or more of them have values that uniquely describe the shape in an External Data window row, then you can automatically link the data row to the shape.

In the example table, Network–Computers, the MachineSN field is the primary key, but it is the SerialNumber shape data row in the PC shape. You can change the column, or columns, that define each row uniquely (usually the primary key) using the Configure Refresh dialog, which can be opened from the right mouse menu of the External Data window.

The unique identifier can consist of multiple columns, just as the primary key in the database table can be comprised of multiple fields.

So, in our example, you can have multiple PC shapes already placed within the drawing page and, if you enter valid Serial Numbers, such as SN10000006, SN10000007, etc., you can merely select Automatically Link… from the right mouse menu of the External Data window. Then, all the shapes with valid SerialNumber/MachineSN values will be automatically linked to the related data row.

Merging Data from Other Sources

Not only can you use multiple Link Data sources per drawing, but you can also have multiple Link Data sources per shape.

Thus, in the network PC example we have been exploring, we can merge the personnel information from the Office–Employee Details table, so each PC displays the Department, Employee Title, and Extension, all useful information for your helpdesk. Select Data | Link Data to Shapes… again, and use the sample database as before but, this time, choose the Office–Employee Details table.

This table contains each employee’s name in the Name column, but this is equivalent to the Belongs To column in our existing PC shape data rows. Therefore, you must rename the Name column as Belongs To in the Column Settings dialog, before we link any data rows.

Also, you can uncheck the XLocation and YLocation columns, as they are not required in this example.

Ensure that you have a few PC shapes linked to rows in the Network–Computers table before selecting Automatically Link… from the right mouse menu of the Office–Employee Details tab in the External Data window. After ensuring that All Shapes on Page is selected, proceed to the link where the Belongs To Data Column equals the Belongs To Shape Field in the Automatic Link dialog.

You should end up with two extra Shape Data rows—Title and Extension—added to each PC shape that was linked.

Only two extra rows are there because both the Belongs To and Department Shape Data rows existed already, so it was unnecessary to create them.

Navigating to/from Linked Data

When a shape in a drawing is linked to a row in an External Data window, it is often useful to be able to find the row or rows from the shape...

...or to find the shape or shapes from the row.

Refreshing the Data

Because you can have multiple Link Data sources per drawing, you may not want to refresh your data from all sources at the same time. Therefore, you can select Refresh Data from the right mouse menu of each External Data Window tab.

Or, you can configure the settings for the refresh with the Configure Refresh dialog that is available from the right mouse menu of the External Data window.

The Configure Refresh dialog enables you to change the data source, which can be useful if the file is moved, or to change the unique identifier. It also lets you set the refresh interval (which must be between 1 and 32,767 minutes) if you want the drawing to be refreshed automatically at all. Finally, you can decide whether any changes the user makes to data in the shape manually will be overwritten from the data source. Remember, this is a one-way link only—you cannot update the data source from the shapes.

Creating Data Connections in Code

You can create DataRecordsets in code, which are presented as tabs in the External Data window in the Visio user interface. Each DataRecordset has DataColumns, each of which are mapped to corresponding fields in the data source.

A DataConnection object is created for each of these DataRecordsets (except for XML data). The DataRecordsets can only be updated from the data source by refreshing the DataRecordset. You cannot update the data from the shapes; it is a one-way connection.

The Data Selector Wizard provides the capability to create simple connections, but you may need to read data via a stored procedure, for example, in a SQL Server database. You can only do this with custom code. The following example has the normal SELECT method commented out, but the alternative Stored Procedure method demonstrates how easy it is to send parameters to the server.

Public Sub CreateRecordset()
Dim dds As Visio.DataRecordset 'The data recordset
Dim ary() As String 'An array to hold the unique identifier columns
Dim SQLConnStr As String 'The connection string for the SQL Server
Dim SQLCommStr As String 'The Command string
Dim datasetName As String 'The dataset name
    SQLConnStr = "Provider=SQLOLEDB.1;" & _
        "Integrated Security=SSPI;" & _
        "Persist Security Info=True;" & _
        "Data Source=localhost;" & _
        "Initial Catalog=Northwind"

    'A SELECT statement
    'SQLCommStr = "SELECT * FROM Customers"
    'ary() = Split("CustomerID", ";")
    'datasetName = "Customers"

    'Or a Stored Procedure
    SQLCommStr = "EXEC SalesByCategory 'Beverages',1996"
    ary() = Split("ProductName", ";")
    datasetName = "SalesByCategory"

    Set dds = Visio.ActiveDocument.DataRecordsets.Add(SQLConnStr, SQLCommStr, _
        Visio.VisDataRecordsetAddOptions.visDataRecordsetDelayQuery, datasetName)
    dds.SetPrimaryKey visKeySingle, ary()
    dds.Refresh
End Sub

The previous example requires the Northwind database to be installed, but it is simple to change it for your own database.

Linking XML Data

The Data Selector dialog offers you five ways of creating data sources, and the capability to select a previously created connection. It does not offer you the chance to select an XML document, but you can link XML, as long as you do it in code.

The XML document must be in “classic” ADO RowsetSchema form. For example, if the following data was stored in a file called PATTested.xml...

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
        <s:AttributeType name='c0' rs:name='_Visio_RowID_' rs:number='1'
            rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='int' dt:maxLength='4' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='SerialNumber' rs:number='2' rs:nullable='true'
            rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='string' dt:maxLength='16'/>
        </s:AttributeType>
        <s:AttributeType name='PATTested' rs:number='3' rs:nullable='true'
            rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='string' dt:maxLength='8'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
    </s:ElementType>
</s:Schema>
<rs:data>
    <z:row c0='1' SerialNumber='SN10000003' PATTested='Yes'/>
    <z:row c0='2' SerialNumber='SN10000004' PATTested='No'/>
    <z:row c0='3' SerialNumber='SN10000013' PATTested='Yes'/>
    <z:row c0='4' SerialNumber='SN10000014' PATTested='Yes'/>
</rs:data>
</xml>

...then the following VBA code would load the file into the Visio document as a new Data Recordset. To run the following code, you need to have a reference to Microsoft Scripting Runtime and Microsoft XML. These can be added from Tools | References in the Visual Basic for Applications (VBA) Editor environment. The Scripting Runtime is required for the FileSystemObject.

Code Listing for CreateXMLRecordSet

Public Sub CreateXMLRecordset()
Dim doc As Visio.Document
Dim dst As Visio.DataRecordset
Dim xmlFile As String
Dim oFS As New FileSystemObject
Dim fil As File
Dim dom As New MSXML2.DOMDocument
Dim OK As Boolean
    Set doc = Visio.ActiveDocument
        xmlFile = "PATTested.xml"
    If Len(Dir(xmlFile)) = 0 Then
        Exit Sub
    Else
        Set fil = oFS.GetFile(xmlFile)
        OK = dom.Load(xmlFile)
        If OK = False Then
            Exit Sub
        End If
    End If
    Set dst = doc.DataRecordsets.AddFromXML(dom.XML, 0, "PAT Tests")
End Sub

Refreshing XML Data

XML DataRecordsets are connectionless (they do not have a DataConnection object), but you can update the source XML file, and then use RefreshUsingXML. The following example code shows how an XML file can be used to update the DataRecordset. It works better if you first edit the PATTested.xml file by altering a PATTested='Yes' to PATTested='No'.

Code Listing for RefreshXML

Public Sub RefreshXML()
Dim doc As Visio.Document
Dim dst As Visio.DataRecordset
Dim xmlFile As String
Dim oFS As New FileSystemObject
Dim fil As File
Dim dom As New MSXML2.DOMDocument
Dim OK As Boolean
    Set doc = Visio.ActiveDocument
        xmlFile = "PATTested.xml"
    If Len(Dir(xmlFile)) = 0 Then
        Exit Sub
    Else
        Set fil = oFS.GetFile(xmlFile)
        OK = dom.Load(xmlFile)
        If OK = False Then
            Exit Sub
        End If
    End If

    For Each dst In Visio.ActiveDocument.DataRecordsets
        If dst.Name = "PAT Tests" Then
            dst.RefreshUsingXML dom.XML
            Exit For
        End If
    Next

End Sub

Link Data Legends

If you publish a data-linked diagram without a legend stating its source, date, and explanation, it is almost worthless. Legends are essential for any business diagram. Unfortunately, there is no legend for the Link Data records, so you may have to create one. The Data Source | Properties right mouse menu displays the name of the data source and how many records are linked in the document (but you cannot see how many are in the active page). You can also see when the Data Source was last refreshed (see the Refreshed value).

I believe you should have a legend for each page with linked data shapes. Therefore, the audience knows when the data was refreshed from the data source. The Visio user can see the External Data window and can find which shapes are linked to which data source. But, the viewer of a printed or Web-published diagram does not have the full Visio client and cannot see where the data came from. Of course, you may not want to display the actual database name or spreadsheet, but you should at least display when each data source was last refreshed.

Two data sets are in the example of the network PCs you have been using: one for the Computers and the other for Employee Details. So, the legend will consist of two rows below the header. I decided there should be a count of the number of shapes linked to each data set.

In this case, both data sets come from the same data source—the sample Access database—but that is not always true.

Creating Link Data Legends

I decided to write a bit of VBA code to display the following details for each data set. You can use the programming language of your choice, so the code can reside in an add-on or COM add-in, but this VBA can just be stored in the Visio document. ALT+F11 gets you into the VBA Editor (or Tools | Macros | Visual Basic Editor), so you can enter the following subfunction into a module or the ThisDocument class.

The code collects a count of shapes linked to each data set in the active page, and then displays the count, the last refresh date, and the data set name and data source filename. It displays the results in a pop-up window and, if you have a shape selected already and choose to do so, refreshes the text with the details.

A suitable shape to add the text to would be a rectangle because you can change the tab stops to make it more legible.

Of course, you can change the code to suit your requirements.

Code Listing for ListActivePageDataLinks and ListPageDataLinks

Public Sub ListActivePageDataLinks()
'Purpose : To call ListPageDataLinks with a shape selected
'to make it into a legend
    ListPageDataLinks Visio.ActivePage
End Sub

Public Sub ListPageDataLinks(ByVal pag As Visio.Page)
'Purpose : To refresh a list of data sets used in a page
Dim drs As Visio.DataRecordset
Dim aryIDs() As Long 'Collects the shape IDs used by a specific RecordDataSet
Dim retVal As Integer 'Return value from MessageBox
Dim txt As String 'List of used DataRecordsets
Dim title As String
Dim shpLegend As Visio.Shape

    'I hate doing On Error Resume Next,
    'but VBA does not have a simple way to test if the array is empty
    On Error Resume Next
    title = "Data Recordsets in Page"
    'Create the header text
    txt = "Items" & vbTab & _
        "Last Refresh" & vbTab & _
        "Name" & vbTab & _
        "File Name"
    For Each drs In Visio.ActiveDocument.DataRecordsets
        ReDim aryIDs(0)
        pag.GetShapesLinkedToData drs.ID, aryIDs()
        'If there is no error, then txt will be appended to
        If Len(drs.CommandString) > 0 Then 
            txt = txt & vbCrLf & _ 
                UBound(aryIDs) + 1 & vbTab & _ 
                Format(drs.TimeRefreshed, "ddddd") & vbTab & _ 
                drs.Name & vbTab & _ 
                drs.DataConnection.FileName 
        Else 
            txt = txt & vbCrLf & _ 
                UBound(aryIDs) + 1 & vbTab & _ 
                "(unknown)" & vbTab & _
                drs.Name & vbTab & _ 
                "unknown XML file" 
        End If 
    Next drs
    'The next statement will fail if it does not find a shape named DataRecordsetsLegend
    Set shpLegend = pag.Shapes("DataRecordsetsLegend")
    If Not shpLegend Is Nothing Then
        shpLegend.text = txt
    Else
        'Optionally update the text of a selected (legend) shape
        If Visio.ActiveWindow.Page Is pag And Visio.ActiveWindow.Selection.Count > 0 Then
            retVal = MsgBox(txt & vbCrLf & "Update text of selected shape?", vbYesNo, title)
            If retVal = vbYes Then
                Visio.ActiveWindow.Selection.PrimaryItem.text = txt
                Visio.ActiveWindow.Selection.PrimaryItem.NameU = "DataRecordsetsLegend"
            End If
        Else
            MsgBox txt, vbOKOnly, title
        End If
    End If
End Sub

Automating Link Data Legends

I have shown you the main principles of creating a Link Data Legend, but you could go further and have a Link Data Legend Master, which you drag-and-drop on to each relevant page in your document. The Link Data Legend Master could be available from a global stencil and this could contain the VBA code to refresh the text.

If you do not like VBA, then you could have a COM add-in that performs the same task, and the COM add-in could be listening for the relevant events in the document, such as DataRecordsetChanged and BeforeDataRecordsetDelete.

An automatic refresh is preferable to a manual one, because people always forget to refresh data. Perhaps the simplest automation is to perform the refresh of legend shapes on all pages whenever the document is saved. This can be done easily in the ThisDocument class in VBA, because it already holds the Visio.Document object with events. All you need to do is enter the following code in the Document_DocumentSaved subfunction:

Private Sub Document_DocumentSaved(ByVal doc As IVDocument)
    Dim pag As Visio.Page
    For Each pag In ThisDocument.Pages
        If pag.Type = visTypeForeground Then
            ListPageDataLinks pag
        End If
    Next pag
End Sub

Consequently, all the shapes called DataRecordsetsLegend have their text updated whenever the document is saved.

Additional Resources

For additional excerpts from this chapter, see the following topics:

Section 2: Labeling Shapes from Shape Data (Visualizing Information with Microsoft Office Visio 2007)

Section 3: Enhancing Shapes with Color, Icons, and Data Bars (Visualizing Information with Microsoft Office Visio 2007)

About the Author

David J. Parker is a Microsoft Visio MVP and the director of bVisual, a Microsoft Certified Partner that provides visual software solutions to a wide range of business sectors and situations.

See Also

Concepts

Book chapter landing page: Chapter 3: Linking Data to Shapes (Visualizing Information with Microsoft Office Visio 2007)