8 Replies Latest reply on Mar 15, 2016 12:16 AM by Roopali Negi

    Drill down org hierarchy

    matt.holty

      I'm looking for help on something that seems simple but is puzzling me how to implement. I'd like to show an org hierarchy for a company starting at the top level with the ability to drill down (and back up) for any employee. However I only want to show a snapshot of direct reports for the lowest level filtered manager. Displayed for these employees would be the employee name, title, level in the company, number of direct reports, number of headcount rolling up to them, and other attributes about that employee. I can manipulate the back end data to any structure (I can build parent/child for each employee as well as manager path for each level. I've already calculated direct reports and employees in an org for each employee) but I can't wrap my head around how to implement in Tableau. Here's a very rough example of how I want it to work (forgive the alignment of the columns):

       

      Employee     Title     Level     Directs     Org Headcount

      Jon Doe       CEO        1          3                94

       

      ---------------

      Drilling down into this employee's direct reports would return something like this:

      ---------------

       

      Employee     Title     Level     Directs     Org Headcount

      Steve Smith    CFO          2          2                   23

      Mark Jones    COO          2          3                 45

      Jane Brown    CTO          2          2                   23

       

      --------------

      And then I could pick one of those employees (like Jane Brown) and drill down to the next level for them:

      --------------

       

      Employee     Title                              Level     Directs     Org Headcount

      Sam Smith    VP                                 3          3               21

      Mike Jones    Executive Assistant         3           0                 0

       

       

      -----------

      I could keep drilling down but I'd also like the ability to go back up a level.

      -----------

       

       

      I've tried figuring this out with filters, calculated fields, and parameters but I can't get anything to work.

       

      Any help would be appreciated. Thanks.

        • 1. Re: Drill down org hierarchy
          matt.holty

          Any ideas? Anyone?

           

          I thought about formatting an employee to have a column for each manager above them in the organization. So employ x at level 3 would have a manager1 and manager2 columns populated and then all other manager level columns would be empty. I though about creating a hierarchy in Tableau and also filters for each level and figuring out how to take the lowest level filter and apply that to the hierarchy to display only those managers. This is most likely a dead end but I'd appreciate any guidance from someone with hierarchy experience.

           

          Thanks,

          -Matt

          • 2. Re: Drill down org hierarchy
            Sai Manikanta N

            Hi Matt,

             

            Can share sample data to get better understanding

             

            -

            Sai

            • 3. Re: Drill down org hierarchy
              matt.holty

              Thanks Sai. I've created a dummy Excel file and attached it to the original post. It contains employee and manager pairings as well as the manager path for each employee. I also used formulas to calculate employee level, direct reports, and org count which are currently being calculated in our database. (These Excel formulas assume each employee name is unique whereas my real data uses employee id to keep them unique).

               

              Also, the dashboard will be updated weekly or monthly and obviously the org is going to change. I cant hard code any calculated field or parameter to a specific employee/manager. (My company has over 32K employees and this would be a nightmare to maintain).

              • 4. Re: Drill down org hierarchy
                Esther Aller

                Hi Matt,

                 

                I found a way to create a drill down hierarchy by:

                1. Create a separate worksheet for each level (listing only employees at that level)
                2. Use action filters on a dashboard that target Select Fields, selecting Employee from the source sheet and the appropriate Mrg Level on the target sheet.
                3. Use the instructions in Dynamically Hiding a Sheet from the Dashboard to collapse the crosstab unless they are selected.

                 

                I have used the provided sample data to create an example workbook. I hope this answers your question!

                • 5. Re: Drill down org hierarchy
                  matt.holty

                  Your workbook is awesome Esther. This is exactly what I was hoping for. Sorry for my delayed response but I had to find time to install Tableau 9 as we use Tableau 8 at work.

                   

                  Question: Are action filters a feature of Tableau 9? I'm unfamiliar with them.

                   

                  I'll try playing around with the workbook you provided and see if I can reproduce it on a new workbook.

                  • 6. Re: Drill down org hierarchy
                    Esther Aller

                    Hi Matt,

                     

                    Action filters are a feature in all Tableau Desktop 8.x versions. This Online Help article Filter Actions has directions on how to create action filters. Below is how I configured one of the action filters I used in the example workbook:

                     

                    action filter example.png

                    1 of 1 people found this helpful
                    • 7. Re: Drill down org hierarchy
                      matt.holty

                      Thank you so much. I had some additional hurdles with my real data set but I was able to implement your solution perfectly.

                      • 8. Re: Drill down org hierarchy
                        Roopali Negi

                        Hi All,

                         

                        I am running into a similar problem and need a solution ASAP. I will be thankful if somebody take time out to answer.My problem is...

                        I have two column/dimensions in a excel worksheet i.e User and Manager..They are related.I imported data from worksheet to Tableau.I want my hierarchy to be starting from Top Manager and going to last user.For example my data is-->

                        User     Manager

                        Jeff Henry

                        Henry John

                        John Garner

                        Garner LLyod

                         

                         

                        I want my Drill down Hierarchy to be-->Llyod-->Garner-->John-->Henry-->Jeff

                        Is it possible to do in tableau???Please have a look into my issue.

                        TIA

                        Lucky