• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

bVisual

  • Home
  • Services
    • How Visio smartness can help your business
    • Visio visual in Power BI
    • Visio Consulting Services
    • Visio Bureau Services
    • Visio Training and Support Services
  • Products
    • Visio Shape Report Converter
    • SS Plus
    • LayerManager
    • visViewer
    • Metro Icons
    • Rules Tools for Visio
    • The Visio 2010 Sessions App
    • Multi-Language Text for Visio
    • Document Imager for Visio
    • multiSelect for Visio
    • pdSelect for Visio
  • Case Studies
    • Case studies overview
    • Using Visio in Education for GIS
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • CNEE Projects, WorldCom
    • Chase Manhattan Bank
  • News
    • Recent news
    • News archive
  • Resources
    • Articles➡
      • ShapeSheet Functions A-Z
      • Comparing Visio for the Web and Desktop
      • Customising Visio Shapes for the Web App
      • Key differences between the Visio desktop and web apps
      • Using the Visio Data Visualizer in Excel
      • Using Visio in Teams
      • Creating Visio Tabs and Apps for Teams with SharePoint Framework (SPFx)
      • Designing Power Automate Flows with Microsoft Visio
      • Innovative uses of Visio Lists
    • Webcasts ➡
      • Visio in Organizations
      • My session and other Visio sessions at MSIgnite 2019
      • Power up your Visio diagrams
      • Vision up your Visio diagrams
      • The Visio 2010 MVP Sessions
    • Visio Web Learning Resources
    • Books➡
      • Visualize Complex Processes with Microsoft Visio
      • Mastering Data Visualization with Microsoft Visio
      • Microsoft Visio Business Process Diagramming and Validation
      • Visualizing Information with Microsoft Visio
  • Blog
    • Browse blog articles
    • Visio Power BI articles
    • Visio for Web articles
    • A history of messaging and encryption
  • About us
    • About bVisual
    • Testimonials
    • Bio of David Parker
    • Contact Us
    • Website Privacy Policy
    • Website terms and conditions
    • Ariba Network
You are here: Home / Visio / Visio 2016 / Aliasing Data Columns for Visio Organization Chart Wizard

Published on January 2, 2018 by David Parker

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!


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 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.


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
 

Related

Filed Under: Excel, Office 365, Org Charts, Shape Data, SQL Server, Visio 2016 Tagged With: Link Data to Shapes, Org Chart, PowerQuery, Visio

About David Parker

David Parker has 25 years' experience of providing data visualization solutions to companies around the globe. He is a Microsoft MVP and Visio expert.

Reader Interactions

Comments

  1. LarryN says

    October 5, 2018 at 4:51 pm

    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.

    Log in to Reply
    • davidjpp says

      November 5, 2018 at 4:48 pm

      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)

      Log in to Reply
  2. Ryan says

    June 19, 2019 at 5:11 pm

    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.

    Log in to Reply
    • davidjpp says

      June 21, 2019 at 11:28 am

      Ryan,
      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?

      Log in to Reply
      • Ryan says

        June 21, 2019 at 3:09 pm

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

        Log in to Reply

Trackbacks

  1. #Visio in #PowerBI for viewing personnel hierarchies and locations | bVisual - for people interested in Microsoft Visio says:
    January 4, 2018 at 3:07 pm

    […] 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 […]

    Log in to Reply

Leave a Reply Cancel reply

You must be logged in to post a comment.

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Co-authoring and Commenting with Visio Documents
  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio

Categories

Tags

Accessibility Add-Ins Connectors Containers Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript LayerManager Layers Legend Link Data to Shapes Lists MSIgnite MVP Office365 Org Chart PowerApps PowerBI PowerQuery Processes Setup and Deployment Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint 2013 SQL Teams Validation VBA Video Visio Visio 2007 Visio for the Web Visio Online Visio Services Visio Viewer Webinar

Footer

bVisual Profile

The UK-based independent Visio consultancy with a worldwide reach. We have over 25 years experience of providing data visualization solutions to companies around the globe.

Learn more about bVisual

  • Amazon
  • E-mail
  • Facebook
  • LinkedIn
  • Twitter
  • YouTube

Search this website

Recent posts

  • Co-authoring and Commenting with Visio Documents
  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio

Copyright © 2025 · Executive Pro on Genesis Framework · WordPress · Log in