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

 

Advertisements

3 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)


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 )

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.

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

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com 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

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: