I need to analyse organizational data for a client. I need to calculate the number of people reporting to each manager and show how it summaries as you work up and down the hierarchy.
My data has a row for each employee with their name and the name of the person who they report to and some financial data such as salary. I need to
1. build the reporting hierarchy from the "reports to" field and then
2. summarise the data (salary, number of people etc) for each node in the hierarchy.
The hierarchy like most organisations changes all the time and is asymetrical (different nodes have different numbers of levels reporting to them).
I have attached a diagram and a simple excel file to demonstrate. The real data has about 1000 rows.
I think it needs some sought of recursive query that essentially build the hierarchy by starting with the
The COE who has a blank "reports to" field and then works through where each person (row in the data) fits into the hierarchy.
Does tableaux have any smarts to help me solve this problem.
Everyone's data is stored a bit differently, but here are a couple examples for flattened HR data and another that uses recursive joins to create the relationships through the HR tree (this is the more common approach). When creating a recursive hierarchy, the main requirement is that the hierarchy must go to a fixed depth. Therefore you must know how many levels anyone could possible go and join the table to itself that many times.
Hopefully this gets you a starting point for how you can structure the data.
While Tableau tries to distance from SQL statements, the computation of depth of hierarchy is solved in may RDBMS by a SQL statement in a self-refrencing table "CONNECT BY PRIOR".
The alternative way to structure the data is to flatten the hirearchy in the same row.
Example: Emplid , SupervisorID, Rollup_Supervisor ID
8567 9001 1001| 2001 | 3001| 9001
2345 2001 1001| 2001
A custom SQL " select * from employee where rollup_Supervisor_ID like '%1001%' " will return all employees reporting to 1001