Creating an Org Chart without the Org Chart Wizard

Microsoft Office Visio has had an Org Chart Wizard for as long as I can remember (and I have been using Visio since 1996).  It has gone through several iterations, but basically it provides a simple way to create personnel hierarchies from a variety of data sources.  It even has a command line interface so that the charts can be created with a minimum of user interaction.  This is great, but it is limited.  Firstly, it does not take advantage of the new Link Data to Shapes and Data Graphics in Visio 207 Professional; secondly, it only does direct reports with just one type of layout; and thirdly, you cannot refresh the diagram easily.  Therefore, I thought it would be interesting to overcome some of these obstacles using the new features of Visio 2007 Professional and with a little code.

Visio installs a sample OrgData.xls Excel spreadsheet in the folder <Program Files>Microsoft OfficeOffice12SAMPLES1033 by default, so I have copied this file and slightly amended it.

image

There are plenty resources on the web to help you use the built-in Org Chart Wizard in Visio, for example : http://office.microsoft.com/en-us/visio/HA011822551033.aspx.  If you follow these in instructions, then you can produce a chart similar to the one shown below.  I have used Color By Value to automatically fill the shapes with different colors by Department.

image

Applying more than the Color By Value Data Graphic can be problematical because these shapes are rigorously controlled by the OrgChart addon, and I have often seen questions about modifying the appearance or layout in the newsgroups.  Customization is also complicated by the fact that there are many master shapes in the stencil, each of which can be changed to look like one of the other.  For example, an Executive can become a Consultant, or a Position can become a Manager.  This is done with the Change Position Type tool, but this does not magically swap one shape for another, but it just changes a property that changes the shapes appearance.  You may think that this is not a problem, except that any customization requires that it be done to all of the master shapes, not just one.

I slightly modified the spreadsheet to insert “mailto:” before the email address.  This will enable Link Data to Shapes to automatically understand that it is a hyperlink and create one on each shape.

image

I used Link Data to Shapes to a display the spreadsheet in an External Data Window in Visio.  Then I simply drew an ellipse and modified its fill, line and shading before dragging one of the records onto it.

image

Once there is some data on the shape, you can use the Data Graphics in Visio 2007 Professional.  I linked the Department to Color By Value again; Master_Shape to the built-in face icons, the Office_Number to a circle at the bottom, and Title, Name and Department as three lines of text in the center of the shape.  The MailTo values automatically create an hyperlink on each shape, as hoped.

image

After showing the Document Stencil, I dragged the shape onto it, and named the new Master as Person.

You can then use this Person master to drag and drop the records from the External Data Window onto the Visio page.  Interesting, but not a very pretty organization chart.

image

Now comes the clever(ish) bit.  The data recordset in the External Data Window is accessible by code.  Therefore, you can loop through the records, get the employee name, then check which other employees report to this employee.   Having got the sub-ordinate employees, you can add a connector between them.

Just for good measure, I take the opportunity to name the shapes, as I loop through them.  This makes the Drawing Explorer Window usable.

The download includes the ConnectSubordinates sub routine in the ConnectShapes module with its support functions, getColumnIndexByName; and IsArrayAllocated.

So, now we have a connected diagram.  Not very pretty, yet, but it is connected.

image

You can manually use Shape / Configure Layout to alter the appearance of the chart

image

Alternatively, you can automate the layout with code.  I have included macros in the LayoutShapes module for changing the appearance in the download, for example, LayoutPageCircular creates a diagram like this:

image

Or LayoutPageCompactTree and LayoutPageRadial creates diagrams like these:

image image

 

You can now choose the type of chart to suit your requirements, and, of course, Visio will automatically optimize the layout if you add in extra matrix reporting.

I have included a short macro, DeleteConnectors, so that the links can be recreated when the data is refreshed. Of course, all of this can be fully automated, along with publishing to the web, PDF or Xps.

The sample files can be downloaded from here:

http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/LinkDataOrgChart.vsd

http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/ORGDATA.XLS

Posted in Visio. 18 Comments »

18 Responses to “Creating an Org Chart without the Org Chart Wizard”

  1. Deepa Says:

    David,
    Question for you. I am trying to automate roadmap creation in Visio. I am a program manager and have different projects in my program with different timelines and group the projects under different threads. For eg
    Quality project 1 start date 12/1/2010 end date 2/1/2011
    Quality project 2 start date 3/2/2011 end date 12/1/2011
    Cycle time Project 1 start date 12/1/2010 end date 2/1/2011 etc
    I have the data in excel,does visio have any plug ins that i can use for this? i have visio 2010.

    I appreciate your help
    Thanks
    Deepa

    • davidjpp Says:

      So, are you trying to display the progress of multiple projects in a single Visio diagram? I have been doing this for several years, but with the Project data merged in XML or exposed as a web-service. We have our own custom add-in to do this, and there is another one available at http://www.visibility.biz/

  2. Patrick Says:

    Deep,Install Sharepoint now:) and start laughing

  3. maureen Says:

    i’m linking to an exchange database to create an organizational chart, but i’d like to include “home” phone number for an emergency contact tree. it seems to be limited to basic information in exchange, and not extended properties. any way to add other fields? thanks

  4. chic Says:

    I cannot download the visio file from your blogs. It is saved on my desktop with *.vsd.ppt extension. Please advice.

  5. Selecting Nodes in Visio PivotDiagrams « bVisual Says:

    [...] Top Posts Data Import & Export Features in Visio 2010A Patch Panel that can be 1U tooToggling Layers On and OffLinking Visio WBS Modeler Diagrams to ProjectUK Counties and Boroughs Map Shapes for VisioHow to Run VBA Macros from a Ribbon Button in Visio 2010AboutUsing the Microsoft Visio Viewer (and introducing visViewer)Deploying a Visio 2007 or 2010 VSTO add-in with Visual Studio 2010Creating an Org Chart without the Org Chart Wizard [...]

  6. karan Says:

    From where can I download the macros?

  7. ErikG Says:

    The ConnectSubordinates subroutine doesn’t work in Visio 2010. I get “This operation is not supported in Mocrosoft Visio Standard 2010″ and debugging brings me to the “Set drs =” line. Ideas?

  8. Peter Downer Says:

    Hello David, this is getting me thinking, I am just starting to try to link Visio Org charts to excel spread sheet data. I have used the wizard to create and then linked the data in the spread sheet to shapes on the diagram. My problem was automatically updating the links to each shape based on data in the spread sheet. It sounds as though you have resolved this though I am currently only grasping the “edges” of what you have said.
    I note you have a book on Visio 2010. Would reading your book provide me with a better understanding of what you have written in this Blog? Or is it on an entirely different set of diagrammatical challenges.
    Great tips anyway…

    • davidjpp Says:

      I do use the Org Chart Wizard AND Link Data to Shapes together.
      Use the Org Chart Wizard to add the of data required to construct the hierarchy (the Name and Reports To data) and the basic display, then use the automatic link option of Link Data to Shapes to add all of the Shape Data you want by selecting the Name (assuming that this is unique) for the Data Column and Shape Field. You have to do the automatic link on each page, but at least you will have the ability of refreshing most of the data (except the hierarchy).
      Alternatively, use the Pivot Table like in my blog, or custom code.

    • davidjpp Says:

      My 2007 book is probably more suitable for the data linking techniques … almost all of it still relevant …. the 2010/13 book is about data validation.

  9. Prashant Says:

    Hi David, is it possible to create org chart using excel vba? I am struggling with it. Any help in this regard is highly appreciated.

    Regards,
    Prashant


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 208 other followers

%d bloggers like this: