topic Organizational hierarchy RLS in Report Server

Organizational hierarchy RLS

Pk3 — Thu, 14 Mar 2024 13:37:39 GMT

Hello everyone,

I'm looking to implement dynamic Row-Level Security (RLS) based on the organizational hierarchy. However, I'm unable to use the Path function to extract the hierarchy since my data isn't structured or cleaned, and the hierarchy information isn't available in the "EMP ID" column. ( some of the IDs are not available in the "EMPID" column but are present in the "Manager ID")Could you please suggest any workarounds for this?

Re: Organizational hierarchy RLS

lbendlin — Fri, 15 Mar 2024 01:25:02 GMT

Dynamic RLS doesn't need hierarchies. All you need is a mapping table between email addresses and permissions.

Re: Organizational hierarchy RLS

Anonymous — Mon, 18 Mar 2024 05:30:53 GMT

Hi @Pk3 ,

For hierarchies that cannot be extracted using the Path function, you can try this:

ParentSearchValue = LOOKUPVALUE ( [Id], [Id], [ParentId] )

The second step is to continue to determine the PATH using ParentSearchValue instead of ParentId.

And when using text columns in PATH(), you need to replace blanks with null.

Below is the "Row-Level Security (RLS) with Organizational Hierarchy" links will help you:

Implementing Row-Level Security with Organizational Hierarchy (copperchips.com)

Dynamic Row Level Security with Organizational Hierarchy Power BI - RADACAD

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.