RLS to show emp data for herself and all her direct/indirect reportees & all emp with same groups

[VeenaAnalyst]

There is one employee table, sales table and department table.

employee table

sales : 

Department table:

if Ana Logged in the report ,Ana should see data for herself and all her direct and indirect reportees : Ana , Chris, Susan

Also Ana should see data for all the employees who is having same department here (dept3): Nick ,Robert

Expected output is:

Immediate help would be appreciated.

@Chakravarthy : You helped me in similar issue earlier. Could you please provide an idea on this as well.

Using RLS employee should see data for his organizational group as well as all his reportees data 

[VeenaAnalyst]

I changed my table and applied few steps to achieve RLS for direct and indirect reportees and it worked. Below is the table structure.

1.If Leia Organa logs in the system she will see data for direct reportees Kylo Ren and Darth Maul and indirect reportees Ana.

2.Now 2nd requirement is Leia Oragana should see data for all records having similar department i.e Dept 3

So It will include Obi-wan .

3.3rd requirement there is another table department where the employees are not enterd in Manager table , but since they belong to some department ,employees from Manager table should see related department data from department table. Robert will be included.

Final output should be 

Highlighted in Green belongs to dept 3. Can any one help me on this

[v-junyant-msft]

Hi @VeenaAnalyst ,

Can you explain how you determined that Chris, Susan and Ana were directly or indirectly connected? I can't understand what the conditions for the determination are based only on this screenshot you provided.

Also, where did this number 5 in your desired outcome come from?

Best Regards,
Dino Tao

[VeenaAnalyst]

@v-junyant-msft , My bad!. Below is the expected output. Instead of 5 it is 4.

Susan reports to Chris and Chris reports to Ana. So they are direct and indirect reportees

Requirement :

When Ana Logged in the report ,Ana should see data for herself and all her direct and indirect reportees : Ana , Chris, Susan

Also Ana should see data for all the employees who is having same department here (dept3): Nick ,Robert

[v-junyant-msft]

Hi @VeenaAnalyst ,

I think you need to add a new table to mark who has direct or indirect connections with whom. Just based on the three tables you provided before, you can't achieve that Ana can see the data of Chris, Susan.

Best Regards,
Dino Tao