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
• WORKING_TITLE > Title
• 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],”@contoso.com”})
• MailTo = Text.Combine({“mailTo:”,[UserName],”@contoso.com”})
• Tel = Text.Combine({“tel:”,[Telephone]})
• Skype = Text.Combine({“im:<sip:”,[UserName],”@contoso.com”,”>”})

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