Structuring a hierarchy "path" in a load statement
Good afternoon everyone,
I was just curious if anyone might have any thoughts on this. I'm currently working on a method to create a hierarchy path for a report to be used across an organization. I'm currently using org levels for section access and ensuring leaders who use the dashboard can only see employees who work in the org levels they manage. An additional requirement would be to allow leaders (managers, directors, VPs, etc.) to be able to not only see their employees, but to be able to filter on the supervisors, managers, directors, etc. that report to them. I have a field for supervisors that can relate information at that level, but that's basically the extent of the existing extract. We don't currently have any fields to relate to each individual employee's managers, directors, etc. on a row-by-row basis. That being said, every employee has a position ID associated with them, and within the same row they have a supervisor position ID.
To fulfill this purpose I was thinking of using a nested if statement with flags like:
The problem with this solution is that while I can manually input each value or even structure a loop to fulfill this purpose, it would be a static table and not dynamic for retirements and/or new hires. I was thinking it would be worth creating a hierarchy "path" field and a hierarchy level field that would basically concatenate and report the position IDs from the existing employee all the way up to the CEO (though I'm not entirely sure how to get to that point). The hierarchy level would basically define their association to the path. Say level 1 for CEO, 2 for executive council, etc.
I'm wondering if anyone might have any suggestions on working towards a solution to complete this task.