How to Create an Org Chart Using Tableau Prep
Updated: Feb 1
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 Tableau Social Ambassador and a Senior Data Analytics and Viz Consultant for Cleartelligence.
This post describes the first of two methods of creating an Org Chart in Tableau. If you haven’t read the post where I give a short summary of the pros and cons of each method, you can find that here. If you would like to download the data, and follow along, you can download my files to follow along here.
In order to create an org chart (or any hierarchy chart or dendrogram) you will need to establish the relationship between each employee and every level of the hierarchy above them, as well as to their own direct reports.
To do this, we will create a row for every relationship. One for each employee-supervisor, all the way up to the CEO, and one for each employee they directly manage.
We will start, as these things usually do, with data. You will need an employees table containing at least the employee’s name, and the name of their manager. Preferably, it will have IDs. It may also have other information about the employee. It will probably look something like this:
For this method, we will end up with a data structure like this:
This will create all of the points we need to build the org chart.
Let’s get started…
The Data Prep
First, we will handle the supervisor hierarchy. This is done using a series of self-joins.
Below, I walk through how this can be done in Tableau Prep because it breaks down the steps clearly. You can also do this in Alteryx or SQL. Ideally, you would use a batch macro (Alteryx) or recursive CTE (SQL), to allow your model to automatically scale with the organization.
Connect to the employees table.
Add a cleanse step to remove all fields except for [Employee ID], [Manager ID], [Employee], and [Manager]. This will make it easier to manage as we build the flow.
Next, we will perform a self-join. Add a cleanse step, and left join the employees table to this cleanse step on [Manager ID] = [Employee ID]. This will bring in the 2nd level manager.
Rename fields: [Manager ID] becomes [Manager ID L1], [Manager] becomes [Manager ID L2] and Remove duplicate fields [Employee] and [Employee ID]
Left Join the employees table again, this time on [Manager ID L2] = [Employee ID]
Rename fields: [Manager ID] becomes [Manager ID L3], [Manager] becomes [Manager ID L3] and Remove duplicate fields [Employee] and [Employee ID]