Aliasing Data Columns for #Visio Organization Chart Wizard

One of the most frequently used components of Visio is the Organization Chart Wizard (OrgChWiz), but it is also one of the most frustrating because of its apparent inflexibility. I was recently asked how to use different table column names with the wizard because the originating ODBC source could not be touched. Well, I have previously used Access to modify and enhance tables in this situation, but the OrgChWiz is quite old, and does not like using the newer Access queries as a data source unfortunately. If the basic table column names do not match the default column names in the organization chart shapes, then there may be superfluous Shape Data rows created on each shape and the ability to change the shape style is compromised, and the Shape Data rows get quite confused!

This slideshow requires JavaScript.

The Organization Chart template opens with the Belts – Organization Chart Shapes stencil open by default, although the Master shapes used can be replaced with those on one of the other nine using the Org Chart / Shapes gallery:

  • Belt
  • Notch
  • Pip
  • Shapetacular
  • Bound
  • Coin
  • Panel
  • Petals
  • Stone
  • Perspective

In addition to changing the style of shape, the Position Type for each shape can be any one of seven, either selected for an individual shape from its right mouse menu option Change Position Type, or for a number of selected shapes using the Org Chart / Shapes / Change Position Type button:

  • Executive
  • Manager
  • Position
  • Assistant
  • Consultant
  • Vacancy
  • Staff

There is a separate master shape for each of these Position Type values, but in reality, they are all the same with a User.ShapeType value between 0 and 6. The Position Type applied by the wizard can be set by having a column called Master_Shape with one of the 7 values in it. This will automatically change the appearance of each shape to suit, and even the layout location, as in the case of Assistant which will layout differently to the others. The User.ShapeType values are:

  • 0  [Executive]
  • 1  [Manager]
  • 2  [Position]
  • 3  [Consultant]
  • 4  [Vacancy]
  • 5  [Assistant]
  • 6  [Staff]

There are some acceptable alternative column names for Master_Shape and other columns, such as Reports_To which can alternatively be named Manager , and will list those in a subsequent article.

In this example, the original SQL Server table has column names that are uppercase and inappropriate for linking to the default Organization Chart shapes which have the default Shape Data rows:

  • Department
  • Telephone
  • Name
  • Title
  • E-Mail

This is subtly different to the Person shape on the Resources stencil which has the default Shape Data rows:

  • Name
  • Title
  • Phone Number
  • E-mail Alias
  • Manager
  • Department

Note to Microsoft : A little consistency would be a good thing!

So, I linked the dbo.Org table into Excel in Sheet1 of a new workbook, and then created a New Query, queryOrg, on the table in Sheet2. I renamed the following columns:

  • NAME > Name
  • POS_POSITION > Master_Shape
  • POS_PAY_GRADE > Grade
  • REPORTS_TO > Manager
  • COST_CENTER > Department
  • PABXID > Telephone
  • NETWORKNAME > UserName
  • ROOM > Room Number

I then added four calculated custom columns, as follows:

  • E-Mail  = Text.Combine({[UserName],””})
  • MailTo = Text.Combine({“mailTo:”,[UserName],””})
  • Tel = Text.Combine({“tel:”,[Telephone]})
  • Skype = Text.Combine({“im:<sip:”,[UserName],””,”>”})

I added the MailTo, Tel and Skype columns to demonstrate how hyperlinks can be automatically added to shapes using Data \ External Data \ Custom Import, although the OrgChWiz can add a single hyperlink automatically too, but this requires a registry hack. The MailTo hyperlink column can also be used in PowerBI, but the others cannot.

This slideshow requires JavaScript.

This organization diagram can now be enhanced by using Data \ External Data \ Custom Import to link the rows from the same Excel workbook query. This can be used to add Data Graphics, such as Color By Value for the Department, although any extra Icon Sets, Text Callouts or Data Bars can interfere with routing the connectors between each shape. Additionally, the hyperlinks can be automatically created if the required String type columns are ticked as Hyperlinks.

This slideshow requires JavaScript.

It is slightly disappointing to see that the Data Selector in Visio only lists the ranges, and not the Excel Names, thus the queryOrg Name is displayed as Sheet2!ExternalData_1 in the drop down list for the workbook, but I could link all my Org Chart shapes to the external data, and thus automatically add hyperlinks. This technique can also be used to update any of the other data too, but it will not cause a re-structuring of the hierarchy.

See Create a Visio organization chart for Microsoft help.

Related articles:

Visio Org Charts with Multiple Languages

Creating a Custom Org Chart Template with Extra Properties

Creating an Org Chart without the Org Chart Wizard

Three short Visio 2016 video tutorials

Visio Shape Data Linking Tips



6 Responses to “Aliasing Data Columns for #Visio Organization Chart Wizard”

  1. #Visio in #PowerBI for viewing personnel hierarchies and locations | bVisual - for people interested in Microsoft Visio Says:

    […] last article, Aliasing Data Columns for #Visio Organization Chart Wizard , mentioned the slight anomalies in the default Shape Data rows for the Organization Chart shapes […]

  2. LarryN Says:

    I cannot figure out how to do this with a sharepoint list data connection, all of the data shapes end up outside of the shape as extra data, not inside the shape. If you have any support on this, that would be magic.

    • davidjpp Says:

      Are you using Data Graphics with the built-in Org Chart shapes? If so, try using the Fields tab on the Options button that can be opened from the Org Chart / Shapes ribbon group (it is the small square button in the bottom right of that group)

  3. Ryan Says:

    Hey David, I have a question/anomaly for you. I’ve created my org charts in Visio 2010 via the Wizard, importing from an excel file, and at first everything is great. I then linked the Data to Shapes, linking to the excel file, and then set to automatically link. Everything appears to work at first, but I come across a recurring anomaly: when I update information in the excel file and then refresh the Visio file, the information in the shape data is correct, but the actual shape DUPLICATES the changes. If I remove an entry on excel, the shape data shows it removed, but it remains on the actual shape, and will again duplicate all the data from that cell.

    • davidjpp Says:

      I am ot sure that I am understanding completely, but if you want to mail me an example, I will take a look at it.
      However, do you have the Configure Refresh / Options / Overwrite user changes to data checked? And what is the Unique Identifier for your data?

      • Ryan Says:

        I discovered the culprit, but not the solution. The problem child is CHAR(10), which is return/line break. In my excel data, I have a cell that contains multiple employees, and I wanted them listed on a separate line each in Visio. Both entering via alt+enter in excel, or combining via textjoin(CHAR(10) produced the same anomaly in Visio in that if you changed any data for that cell, Visio would just duplicate the data (exponentially for each subsequent change). I easily reproduced this many times in trying to find a solution, simply creating a very basic org chart in excel and use alt+enter to enter multiple lines in one cell, use the sheet in Visio and link the shapes and set to automatically link, then alter any of the data in the aforementioned excel cell. For what it is worth, any/all other characters seem fine and don’t cause the issue. I found a workaround using textjoin(” “, which introduced 10 spaces between each entry (rather than a simple return). This allowed Visio to (usually) put each entry on a separate line in the shape if I sized it properly.

        Even though I’ve found a workaround, I would like someone else to experience this anomaly and see if they can find a solution to make a carriage return work in the data. To see the Shape Data have exactly what it should be, but the actual shape to have multiple duplicates of everything is very…interesting (frustrating).

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Smart graphics for visual people

%d bloggers like this: