500 likes | 954 Views
Visualizing Information the Smart (Diagram) Way. David J Parker MVP (Visio) bVisual ltd. Who am I?. Microsoft MVP (Visio) Microsoft MCP (Visio, SQL) Pre-Microsoft acquisition Visio Business Partner, 1996-8 Speaker at Visio Conferences 1997 &1998 Started bVisual in 1998
E N D
Visualizing Information the Smart (Diagram) Way David J Parker MVP (Visio) bVisual ltd
Who am I? • Microsoft MVP (Visio) • Microsoft MCP (Visio, SQL) • Pre-Microsoft acquisition • Visio Business Partner, 1996-8 • Speaker at Visio Conferences 1997 &1998 • Started bVisual in 1998 • Post-Microsoft acquisition • Microsoft Visio Solution Provider since 1999 • Speaker at Microsoft Office Visio Conferences 2006 & 2008 • Visio Solution Provider Training for Microsoft EMEA • Visualizing Information with Microsoft Office Visio 2007 • McGraw-Hill, 2007 • http://www.visualizinginformation.com • DataLegends – Visio add-in to add Data Graphic legends • http://www.visiotoolbox.com/productdetail.aspx?productid=9 • WBS Modeler for Visio and Project • www.wbsmodeler.com • Visio Blog • http://bvisual.spaces.live.com/
Business Data Visualised bVisual Integration Visio Graphics Enterprise Data
Data Features in Visio On-line Visio Drawing Shape Data Source Prints Graphics odc Data Reports Import > Link > Display > Communicate
Visio 2007 EditionsVisual data • Microsoft Office Visio 2007 Standard • Database Wizard • Microsoft Office Visio 2007 Professional • Database Wizard • Database reverse engineering • UML models • Link Data To Shapes * • PivotDiagrams * • Data Graphics *
Visible DataVisio Professional 2007 • Shape Data • Renamed from Custom Properties • Link Data to Shapes • Multiple data sources per shape • Read only • Uses Office Data Connections (*.odc files) • PivotDiagrams • Diagrammatic version of PivotTables • Uses Office Data Connections (*.odc files) • Data Graphics • Data sensitive text and graphics on shapes
Visio Development Platform .NET Primary Interoperability Assemblies (PIAs) Save As Web Sample code Visio Object Model (COM) Full Visio Client App Visio Drawing Control (ActiveX) Extensibility Support Visio SDK Data Diagramming ML Tools & Wizards VSTO Add-in COM Add-in Add-ons Visio Viewer VBA ShapeSheet, Smart Shapes ShapeStudio Visio Drawings Visio Engine
Importing Linked Data • DataConnection and DataRecordsets • Visio 12.0 Type Lib • Add, AddFromConnectionFile, AddFromXML • UI and code links to: • Microsoft Office Excel & Access • SharePoint Services Lists • SQL Server Tables and Views • OLEDB & ODBC • Can also create link in code to : • SQL Server stored procedures • XML files
Link to SQL Server Stored ProcDeclarations & Connection String • DimddsAsVisio.DataRecordset'The data recordset • Dimary() As String ‘Array to hold the p key columns • DimSQLConnStrAs String 'The connection string • DimSQLCommStrAs String 'The Command string • DimdatasetNameAs String 'The dataset name • SQLConnStr = "Provider=SQLOLEDB.1;" & _ • "Integrated Security=SSPI;" & _ • "Persist Security Info=True;" & _ • "Data Source=.;" & _ • "Initial Catalog=AdventureWorks;" & _ • "Use Procedure for Prepare=1“
Link to SQL Server Stored ProcCommand String & Adding DataRecordset • SQLCommStr = "EXEC uspGetEmployees 1" • ary() = Split("EmployeeID", ";") • datasetName = "Employees“ • Setdds = Visio.ActiveDocument.DataRecordsets.Add( _ • SQLConnStr, SQLCommStr, _ • VisDataRecordsetAddOptions.visDataRecordsetDelayQuery, _ • datasetName) • dds.SetPrimaryKeyVisPrimaryKeySettings.visKeySingle, ary() • dds.Refresh • Visio.ActiveWindow.Windows.ItemFromID( _ • visWinIDExternalData).Visible = True
Link to XML fileConnection String • Dim doc AsVisio.Document • DimdstAsVisio.DataRecordset • DimxmlFileAs String • DimdomAs New MSXML2.DOMDocument • Dim OK As Boolean • Set doc = Visio.ActiveDocument • xmlFile = “MyXMLFile.xml" • OK = dom.Load(xmlFile) • Set dst = doc.DataRecordsets.AddFromXML( _ • dom.XML, 0, “Any Name")
Refreshing Linked Data • UI and code refreshing of DataRecordset • .Refresh • .GetAllRefreshConflicts • .GetMatchingRowsForRefreshConflict • .RemoveRefreshConflict • XML files can only be refreshed in code • .RefreshUsingXML • Automation requires assigned name and file
Link to XML fileRefreshing • Dim doc AsVisio.Document • DimdstAsVisio.DataRecordset • DimxmlFileAs String • DimdomAs New MSXML2.DOMDocument • Dim OK As Boolean • Set doc = Visio.ActiveDocument • xmlFile = “MyXMLFile.xml" • OK = dom.Load(xmlFile) • For Each dstInVisio.ActiveDocument.DataRecordsets • Ifdst.Name = “Any Name" Then • dst.RefreshUsingXML dom.XML • Exit For • End If • Next
Linking Shapes from External Data Window • Select a Master – Drag data row(s) • Drag a data row onto any shape • Shape Data key field(s) contain values - AutoLink
External Data on ShapesWhat the user sees • Shape Data automatically added to shapes • Basic Data Graphics usually added by default
External Data on ShapesBehind the scenes • User-defined Cells • Shape Data • _VisDM_ prefixes column names • Unless already exist!
demo Importing Data
Create Linked Shapes in CodeDefinition • DropManyLinkedU • ObjectsToInstance() array of variants • XYs() array of doubles • DataRecordsetIDlong • DataRowIDs() array of longs • ApplyDataGraphicAfterLinkboolean • ShapeIDs() out array of longs
Create Linked Shapes in CodeExample • 'Retrieve the row ids from the datarecordset • DimaryRowIDs() As Long • aryRowIDs = drs.GetDataRowIDs("") • 'Drop the master in random co-ords, and linked to the datarecordset, applying a DataGraphic • DimaryShapeIDs() As Long • Dim applyDataGraphics As Boolean • Visio.ActiveDocument.DocumentSheet.Cells( _ • "User.msvLastAppliedDataGraphic").FormulaU = _ • "=""Data - Employees""" • applyDataGraphics = True • Visio.ActivePage.DropManyLinkedU _ • aryMsts, aryXYs, drs.ID, aryRowIDs, _ • applyDataGraphics , aryShapeIDs
demo Linking Data to Shapes Drop Many...
Connecting Shapes from DataUsing DataRecordsets in code • Many types of diagrams require shapes to be connected • Organization Charts, Network diagrams, Process • Connecting shapes example code • ConnectSubordinates • ConnectDottedLineManagers • Can then use different layouts for display • LayoutPage... example code • LayoutSelection... example code
ConnectSubordinatesCode excerpt • ForiEmp = 0 ToUBound(aryShapeIDs) • 'Get the potential manager row • SetshpEmp = Visio.ActivePage.Shapes.ItemFromID(aryShapeIDs(iEmp)) • rowID = shpEmp.GetLinkedDataRow(drs.ID) • varRowData = drs.GetRowData(rowID) • 'Arrays are zero based • empID = CLng(varRowData(empColumn - 1)) • 'Filter the rows to get sub-ordinates of the current employee • aryRowIDs = drs.GetDataRowIDs("ManagerID = " & CStr(empID)) • 'Iterate thru the subordinate datarecordset rows • ForiSub = 0 ToUBound(aryRowIDs) • v = drs.GetRowData(aryRowIDs(iSub)) • 'Get the subordinate shapes • Visio.ActivePage.GetShapesLinkedToDataRow drs.ID, aryRowIDs(iSub), arySubShapeIDs • 'Check that there is actually something in the array • IfIsArrayAllocated(arySubShapeIDs) = True Then • 'Iterate thru the subordinate shapes • ForiSubShape = 0 ToUBound(arySubShapeIDs) • SetshpSub = Visio.ActivePage.Shapes.ItemFromID(arySubShapeIDs(iSubShape)) • 'Create a dynamic connection from manager to subordinate • shpEmp.AutoConnectshpSub, visAutoConnectDirUp • NextiSubShape • End If • Next iSub • NextiEmp
Hierachichal Layouts • Standard Hierachy Org Chart Page Layout • Modified with Selection Layout • cf Org Chart Wizard
Alternative Layouts • Other Page Layouts may also be suitable • Compact Tree • Radial • Circular
demo Connecting Shapes from Data Reading Data and Layouts
Using the Connection to Write • 'Get the DataRecordset • DimdrsAsDataRecordset • Setdrs = Visio.ActiveWindow.Windows.ItemFromID( _ • Visio.visWinIDExternalData).SelectedDataRecordset • ... • ‘Get the connection string • DimconStrAs String • conStr = drs.DataConnection.ConnectionString • ‘Use the connection string to create a connection • DimdbCnxnAsNewADODB.Connection • dbCnxn.ConnectionString = conStr • dbCnxn.Open • dbCnxn.BeginTrans • ...
demo Writing Data from Shapes
Data GraphicsOverview • Color By Value • Text • Data Bar • Icon Set
DataGraphic MastersWhat the user sees • Document stencil only shows visible masters • Drawing Explorer displays all (but not type)
DataGraphic MastersBehind the scenes • Public Sub EnumMasters() • DimmstAsVisio.Master • Debug.Print "ID", "Hidden", "Type", "IsDGCallout", "Name" • For Each mstInVisio.ActiveDocument.Masters • Debug.Print mst.ID, mst.Hidden, mst.Type, _ • mst.Shapes(1).IsDataGraphicCallout, mst.name • Next mst • End Sub
Master and Instances Document Page Document Stencil Normal Master Master instance with DataGraphic GraphicItem Master DataGraphic Master
Creating DataGraphics in code • Create a new DataGraphic master • Document.Masters.AddEx(visTypeDataGraphic) • Open the new Master • Master.Open • Copy an existing GraphicItem to it • .GraphicItems.AddCopy(existingGI) • Amend GraphicItems • .HorizontalPosition & .VerticalPosition • .SetExpressionVisGraphicField, string • Close Master
Min & Max Values for Data BarsSetDataBarMinMaxValues code • A Data Bar graphic item can be • Multiple times in same Data Graphic • In multiple Data Graphics • Each instance has different value ranges • Min and max values read from shape instances • Prop. msvCalloutField • Data Bar can have multiple fields • E.g. Multi-bar graph • Prop. msvCalloutPropFieldnwhere n=2 to 5 • Update master using .Open
Setting Min & Max Values Document Stencil Document Page 5. Update DataGraphic Master Data Bar GraphicItem Min/Max Values 4. Read Min/Max of Data Bar Graphic Items 2. Collect Data Bar GraphicItems 1. Get Active DataGraphic Master 3. Create Selection
SetMinMaxDatabarValuesStart Part • Const UserTypeAs String = "User.msvCalloutType“ • Const UserDGIDAs String = "User.visDGItemID " • Const PropMaxAs String = "Prop.msvCalloutPropMax" • Const PropMinAs String = "Prop.msvCalloutPropMin“ • 'Get the active Data Graphic • DimmstDGAs Master • SetmstDG = GetActiveDataGraphic • imdicDataBarShapesAs New Dictionary • DimgiAsVisio.GraphicItem • 'Create a dictionary of data bars • For Each gi In mstDG.GraphicItems • Ifgi.Type = visTypeDataBar Then • dicDataBarShapes.AddCStr(gi.ID), 0 • End If • Nextgi
SetMinMaxDatabarValuesMiddle Part • DimcolDataBarShapesAs New Collection • DimitmGraphicAsVisio.shape • DimgIDAs String • 'Update the data bars dictionary with shape pseudo-index • For Each itmGraphicInmstDG.Shapes(1).Shapes • IfitmGraphic.IsDataGraphicCallout = True Then • IfitmGraphic.Cells(UserType).ResultStr("") = "Data Bar" Then • gID = CStr(itmGraphic.Cells(UserDGID).ResultInt("", 0)) • dicDataBarShapes.Item(gID) = itmGraphic.NameU • colDataBarShapes.AddgID • End If • End If • Next itmGraphic • 'Iterate thru each page to collect min/max values • For Each pagInVisio.ActiveDocument.Pages • 'Get all shapes that use this data graphic master • Setsel = pag.CreateSelection(visSelTypeByDataGraphic, 0, mstDG) • ... • Nextshp • Nextpag
SetMinMaxDatabarValuesEnd Part • ... • 'Finally - update the ItemGraphic in the DataGraphic Master • DimmstCopyAsVisio.Master • SetmstCopy = mstDG.Open • For Each itmGraphicInmstCopy.Shapes(1).Shapes • If itmGraphic.IsDataGraphicCallout = True Then • IfitmGraphic.Cells(UserType).ResultStr("") = "Data Bar" Then • gID = CStr(itmGraphic.Cells(UserDGID).ResultInt("", 0)) • itmGraphic.Cells(PropMax).FormulaU = "=" & dicMaxVal.Item(gID) • itmGraphic.Cells(PropMin).FormulaU = "=" & dicMinVal.Item(gID) • End If • End If • NextitmGraphic • 'Close the copy to update all instances • mstCopy.Close
demo Data Graphics Updating DataBar Minimum and Maximum Values automatically
PivotDiagramBreakdown • Breakdown data into Categories • Sort • Merge • Promote • Collapse
PivotDiagramBreakdown • PivotDiagram is an add-on • Not an API extension • Only launched in code from SharePoint Services List (soon to be published MSDN article) • Use code to select in various ways • Aids in applying DataGraphics and extra DataRecordsets • E.g. SelectSameBreakdownName sample
SelectSameBreakdownName • ... • SetvsoWindow = Visio.Application.ActiveWindow • IfvsoWindow.Selection.Count > 0 Then • Setshp = vsoWindow.Selection.PrimaryItem • IfIsPivotNode(shp) = True Then • breakdownName = _ • GetGroupNameByID(GetBreakdownID(shp)) • SelectByBreakdownNamebreakdownName • End If • End If
IsPivotNode & isPivotShapeType • IsPivotNode = isPivotShapeType(shape, ePivotShapeType.Node) • Private Function isPivotShapeType(ByVal shape AsVisio.shape, _ • ByValpivotShapeTypeAsePivotShapeType) As Boolean • IfNotshape.CellExists(UserDDShapeType, _ • Visio.VisExistsFlags.visExistsAnywhere) = 0 Then • If shape.Cells(UserDDShapeType).ResultIU = pivotShapeTypeThen • isPivotShapeType = True • Else • isPivotShapeType = False • End If • Else • isPivotShapeType = False • End If • End Function
demo PivotDiagrams Custom Selections
CustomisationDataLegends • Legends for Icon Sets & Color By Value
demo DataLegends
CertificationIndividual • TS Exam 70-545 • Microsoft Office Visio 2007, Application Development • http://www.microsoft.com/learning/exams/70-545.mspx • Deploying Visio Solutions • Working with External Data • Developing Visio Libraries • Automating Visio Diagrams • Publishing and Viewing Visio Diagrams
CertificationCorporate • New Microsoft Certified Partner Specialization • Information Worker Competency • Data Visualization Specialization • https://partner.microsoft.com/40039095 • Need 2 individuals that have passed Visio TS exam
Visio Development Resources • Visio 2007 SDK • http://msdn2.microsoft.com/en-us/library/ms409183.aspx • Visio 2007 Solution Development Workshop • http://www.microsoft.com/downloads/details.aspx?FamilyID=332c211e-581e-4029-9839-8f45de10b4c2&DisplayLang=en • Visio Developer Portal • http://msdn2.microsoft.com/en-us/office/aa905478.aspx • Visio Insight blog • http://blogs.msdn.com/visio/ • Visio MVP web site • http://visio.mvps.org/ • My Visio Blog • http://bvisual.spaces.live.com/
Thankyou! David J Parker Microsoft MVP (Visio) http://www.bVisual.net Book http://www.visualizinginformation.com davidp@bvisual.net