1 Reply Latest reply on Mar 30, 2018 2:42 PM by Ivan Young

    Calculated field to create Employee Hierarchy path for employee Sachin Tendulkar - Found a possible solution, but it didn't work out, need help

    Raj Kumar

      Background:

       

      There are 11 dimensions:

      Level1Name  (This has the name of CEO)

      Level1ID (Employee ID of CEO)

      Level2Name (This has names of VPs, who report to CEO)

      Level2ID (Employee ID of VPs)

      Level3Name (Manager Names, who reports to VPs)

      Level3ID (Employee ID of Managers)

      Level4Name (Employee name, who reports to managers)

      Level4ID (Employee ID of Employees)

      Employee Name

      Employee Level (Levels are from 1-4)

      Employee ID

       

      Requirement:

       

      Create a column that should show the hierarchy of employees.

      Example: When Employee ID, Employee Name, Employee Level and Employee Report Path (this needs to be created) are dragged to rows shelf, the following values should be shown.

       

      Employee ID     Employee Name   Employee Level   Employee Report Path

      0000000234     Sachin Tendulkar          3                   \CEO Name\VP Name\Sachin Tendulkar

       

      Solution Tried:

       

      IF [Employee ID] = [Level1ID] then  "\" + [Level1Name]

      ELSEIF [Employee ID] = [Level2ID] then  "\" +[Employee Name Level1] + "\"+[Level2Name]

      ELSEIF [Employee ID] = [Level3ID] then  "\" +[Employee Name Level1] + "\"+[Level2Name]+ "\"+[Level3Name]

      ELSEIF [Employee ID] = [Level4ID] then  "\" +[Employee Name Level1] + "\"+[Level2Name]+ "\"+[Level3Name]+ "\"+[Level4Name]

      END

       

      When this calculated field is dragged to rows shelf, it failed with timeout error. Please let me know if you can think of any idea to get this resolved.

       

      Thanks

      Raj