3 Replies Latest reply on Sep 20, 2018 3:26 PM by donny.gore

    Filter or calculate all employees(at all levels) whom report to X employee.

    Rick H

      I am a newer Tableau user, and I am struggling with how to even start going about a solution to a question.  I apologize for the broad "how do I do this" question.  I have done some searching, but may not be wording my needs properly to produce the results needed.

       

      - Tableau 10.5

      - connection to SQL.

      - flat data structure

       

      Task:

      - I am looking to create a way to filter my employee list by all employees which report to any chosen employee.

      - My data includes employee ID and manager ID for all employees, regardless of level they are at.

      - Solution needs to filter through up to 8 levels of employee hierarchy.

      - Some filters would return 1000+ employees.

       

      I have included a very basic example with some flat employee/manager data.  How would I go about being able to filter on employees that report up to manager 10018 for example?

        • 1. Re: Filter or calculate all employees(at all levels) whom report to X employee.
          donny.gore

          Take a look at the attached and see if it's going the direction you're wanting to go.

           

          What I did was duplicate the Sheet1 data and perform an inner join between Sheet1.[Manager ID] and Sheet11.[Employee ID]

           

          Then, I created a parameter for all the values in the Sheet1.[Manager ID] column.

           

          Finally, I created a filter to check if the value selected from the parameter matched the values for Employee, Mgr Level 1, or Mgr Level 2, and if they do, show those rows.

           

          It ends up looking like this; I've selected 10013 as the Manager Parameter and the results are consists of any records where 10013 appears as

          • The Employee ID value
          • The Mgr Level 1 value
          • The Mgr Level 2 value

          • 2. Re: Filter or calculate all employees(at all levels) whom report to X employee.
            Rick H

            Donny - Thanks for the quick reply.

             

            This seems to work for one additional level in my sample data.  That said, these inner joins would have to be done multiple times to go down multiple levels..... correct?

             

            The data I need to pull from is a live SQL connection, with quite large databases and I am already doing a few joins.  I am afraid creating duplicate data sources is not a feasible option here.

             

            Is there any alternative way to accomplish without creating duplicate sheets?

            • 3. Re: Filter or calculate all employees(at all levels) whom report to X employee.
              donny.gore

              Yes, you would essentially need to do additional joins for additional levels. Unfortunately, from within Tableau directly, I don't have any additional suggestions.

               

              When I've had to do something similar, pull out 4 levels of management, I ended up doing 3 self-joins with the table to get the results as columns in the dataset. It's, more or less, the same concept I mentioned before, just doing it from within the query itself. If you're able to use custom sql for the datasource, writing the query so it's bringing the full hierarchy may be worth at least exploring.

               

              Basic query I used looked like this

               

              select

              em.domainname,

              em.manager as mgrlvl1,

              mgr2.manager as mgrlvl2,

              mgr3.manager as mgrlvl3,

              mgr4.manager as mgrlvl4

              from em

              inner join em mgr2 ON mgr2.domainname = em.manager

              inner join em mgr3 ON mgr3.domainname = mgr2.manager

              inner join em mgr4 ON mgr4.domainname = mgr3.manager

               

              There are likely other, better ways to accomplish it, but that was the best I could come up with when I needed it.