Hi im having a big complex problem creating a Organizational RLS Hierarchy for a PowerBi report. Since I can not share any of the data ive asked ChatGPT to sum it up for me and maybe explaine it better then i could. Here is what he wrote.
"
The Problem
I have a company hierarchy where:
- Managers should see all employees under them.
- Employees should only see themselves and their direct teammates.
- Special cases:
- Luke Skywalker & Leia should see each other's direct reports but not employees from other branches(/create these special connections for people without re writing the whole code.).
- Higher managers (e.g., Yoda) should see everything.
- Teammates should be able to see eachother
The Organizational Structure
UserID ParentID HierarchyKey
| Yoda | NULL | /Yoda |
| Obi-Wan | Yoda | /Yoda/Obi-Wan |
| Darth Vader | Obi-Wan | /Yoda/Obi-Wan/DarthVader |
| Luke Skywalker | Obi-Wan | /Yoda/Obi-Wan/LukeSkywalker |
| Leia | Yoda | /Yoda/Leia |
| Darth Maul | Leia | /Yoda/Leia/DarthMaul |
| Kylo Ren | Leia | /Yoda/Leia/KyloRen |
| Mace Windu | Yoda | /Yoda/MaceWindu |
| Qui-Gon Jinn | Mace Windu | /Yoda/MaceWindu/QuiGonJinn |
| Anakin Skywalker | Mace Windu | /Yoda/MaceWindu/AnakinSkywalker |
What I Want to Achieve with RLS Yoda sees everything (since he's the top manager). Each branch manager (Obi-Wan, Leia, Mace Windu) sees their own branch’s employees. Employees see only themselves and their direct teammates. Specially selected people can see each other's employees but NOT other branches.
"
I hope this explains it good enough, and if that isnt clear im trying to do this in a way that will have a low mantinance. Idealy only to add these special roles where some managers can see each others "Childern".
