Can anyone help with pointing me in the right direction towards extending RLS beyond what I'm allowed to view?
So, I have the following structure:
Now I was able to set up RLS for a user to view hers and immediate direct employee reporting to her (for eample Emp B can view Emp B, Emp C & Emp D). I achieved this with an OR statement using 'userprincipalname()' in the 'Manage Roles' section (employee ID = userprincipalname() || manager ID = userprincipalname() or the employee dimension table. Works fine.
My challenge is, still using Emp B example, I want to be able to view next levels as well. That is, to be able to view results of Emp E, Emp F & Emp G via drill through if I so wish (?).
I guess the question is, is this even possible with plain vanilla RLS? In the employee dimension table I have created columns using the PATH functionalities believing therein may be the answer but I'm stuck.
In a nutshell, as an employee being able to view data at my level & further down the hierarchy chain from my level. Is there another way other than RLS?
Can you help?
Many thanks for your time
PS I couldn't attach a sample file. Happy to do so if I figure out how
@avanderschildenI wanted to share update (and solution) on this issue.
By accident I came across a solution the fits nicely to what I was looking for. I found it in a post from Radacad:
Again, many thanks for helping out.
You need a table with unique employees and their email/login. This will be the starting point of the RLS filter;
Emp1 | firstname.lastname@example.org
Emp2 | email@example.com
Emp3 | firstname.lastname@example.org
Emp4 | email@example.com
The second table you need is a table with employees and their children, or parents and all employees they have access too;
Emp1 | Emp1
Emp1 | Emp2
Emp1 | Emp3
Emp2 | Emp3
Emp2 | Emp4
Now, Emp1 will filter the second table and only filter out Emp1, Emp2 and Emp3. No access to Emp4.
The last table is your transaction table with sales or whatever, and this will be connected to table 2 based on employee;
2019-01-01 | Emp2 | 50 Sales
2019-02-02 | Emp4 | 100 Sales
Now, Emp1, will only see a total number of 50 sales, since there is no access to the second row of the transaction table.
Hope this helps to model your data in the right way.
Thanks for your quick response @avanderschilden
This is exactly where I got stuck: Emp 1 in your use can see Emps 2 & 3.
But because Emp 4 reports to Emp 2 who in turn reports to Emp 1, I want Emp 1 to be able to see figures for Emp 4 as well. Kind of like a waterfall RLS from the top node to the most bottom node in that chain for Emp 1
@avanderschilden It's a good idea worth trying. I was avoiding any manual maintenance (the employee file comes from a separate system) that I didn't think of this. I'll simply carve out a 2 column solution with Power Query and let you know how it goes.