The following blog by Jacqui Moore was originally published on Do Mo(o)re With Data July 1, 2022 and is cross-posted here with permission. Jacqui is Tableau Social Ambassador and a Senior Data Analytics and Viz Consultant for Cleartelligence.
Many of us have been asked at some point to build an org chart, or something like it, in Tableau. And, like most of you, I started off with some ideas on how it could work, played with it a little bit, and then went to trusty ol’ Google, to see what the community had come up with. And as usual, the community delivered. I found two posts that set me on the right direction, even though they weren’t quite working for what I needed to do. So, credit, and a huge thank you to Jeff Schaffer, for his post on the subject from 2017, and to Nathan Smith for his post.
Starting with the data…
In order to build an org chart, you will need, at minimum — two fields:
Ideally you will have unique IDs for these records, and additional information such as departments and titles. But those two fields are all you really need.
Next, you will need to shape your data to create the hierarchical relationships between the Employee, their direct subordinates, and all of their supervisors. There are two approaches you can take to model the data. Whether you can transform the data using Tableau Prep, Alteryx, SQL, etc. will probably be the main factor in the decision. Both methods will produce the same end result from the user’s perspective.
Method 1: Preparing the data outside of Tableau Desktop
Using this method, we will prepare the data in Tableau Prep* to create a table that has one record for each employee-supervisor, and one record for each employee-subordinate relationship. We will then use the output to build the org chart visual in Tableau Desktop.
*I’ve used Prep to demonstrate because it does a nice job of visually showing what is happening, and many Tableau Creators have access to Tableau Prep. You can use the same concepts in your data prep tool of choice.
Pro: If the hierarchy becomes deeper, you can make the change once in the workflow and the Tableau dashboard will not need to be updated to scale with your organization. (If using Alteryx or SQL, this can be fully automated)
Con: You need the ability and access to use a data preparation tool and refresh the data on a schedule.
Method 2: Preparing the data in Tableau Desktop
Using this method, we will create a data source in Tableau Desktop with one record for each employee with one column for each supervisor in the hierarchy, and one record for each employee-subordinate relationship. We will then use the data source to build the org chart visual.
Pro: You an do all the data preparation you need right within Tableau Desktop, with no other tools or schedulers necessary.
Con: There will be more to update in the event the organizational hierarchy gets deeper.
The end result
What I ended up with was an interactive org chart dashboard that thrilled my stakeholders, complete with name search and PDF downloads, and a lot of interactivity. I’ve published a couple of variations with fewer bells and whistles to my Tableau Public profile.
An interactive org chart navigator dashboard:
And, a static vertical layout for printing to PDF: