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