How to customize a report for dynamic role assignment?

The version is 7.1.3

What do you want to have a result? A list of Business Roles for a given UID_Department based on what? Did you assign a department to the role via Org.UID_Department and built the dynamic query based on that? Or did you assign people from more than one department to the business roles?

We assigned the business role via Dynamic Role to make people under department A, B or C to have the business role, and consequently obtain the group or resources attached to the business role via inheritance.

Below is the WhereClause for one of the role for example:

(UID_Department in ('2503ae6b-8dbf-4c77-b6a6-46539b633ae4')) or (UID_Department in ('7a51c75a-9f12-411b-9aef-012cd4560274', '6a565212-4063-428e-bc5e-fb1c422d4583')) or (UID_Department in ('e9bf6660-9670-4451-b449-bb9c64cde220'))

We need to print all dynamic roles for auditor review in a reader friendly manner, so is looking for a report can show what departments are linked with what business roles

A SQL solution that assumes that any person assigned to a department that is dynamically assigned to a business role would match your auditors request as roles assigned to departments would look like this.

Select o.Ident_Org, o.FullPath, d.DepartmentName, d.FullPath from Org o 
Join DynamicGroup dg on o.XObjectKey = dg.ObjectKeyBaseTree
Join PersonInOrg pio on O.UID_Org = pio.UID_Org AND pio.XOrigin & 4 = 4
Join Person p on pio.UID_Person = p.UID_Person
Join Department d on p.UID_Department = d.UID_Department
Group By o.Fullpath, o.Ident_Org, d.UID_Department, d.DepartmentName, d.FullPath

A SQL solution that assumes that any person assigned to a department that is dynamically assigned to a business role would match your auditors request as roles assigned to departments would look like this.

Select o.Ident_Org, o.FullPath, d.DepartmentName, d.FullPath from Org o 
Join DynamicGroup dg on o.XObjectKey = dg.ObjectKeyBaseTree
Join PersonInOrg pio on O.UID_Org = pio.UID_Org AND pio.XOrigin & 4 = 4
Join Person p on pio.UID_Person = p.UID_Person
Join Department d on p.UID_Department = d.UID_Department
Group By o.Fullpath, o.Ident_Org, d.UID_Department, d.DepartmentName, d.FullPath

Hi Markus,

Thanks for looking into this. This SQL query actually extracts the department info from the person who had been assigned the role. We have had similar report already.

What exactly needed is to print the rules set in the dynamic groups, so auditor can verify the documented policies have been properly configured in the system.

Did you create the queries using the filter wizard at the dynamic role or using free SQL where clauses?

The condition was created using wizard, for the convenience for operator to maintain the list

Try this one:

Worked like a charm!

Thanks Markus!