Filter a report on sys_user_has_role by assignment group

[Max Nowak]

Hi,

I have the requirement to create a report of users with a specific role which should be filterable by group. The use case of this is "Show every customer service agent who is responsible for X", where the responsibility for X is determined via group membership in specific assignment groups.

So, to start off, I created a report based on sys_user_has_role, with the condition of "role = sn_customerservice_agent". This satisfies the first requirement of showing a list customer service agents, and the requirement was to make this list filterable by assignment group, so I thought about creating an interactive filter on a dhasboard.

What I want to do is being able to select a certain group in an interactive filter, and then showing only users who belong to that group - but I'm not sure how to implement this with interactive filters. The only filter that seems to be applicable is the reference interactive filter, but I don't have a direct reference - my base table is sys_user_has_role, which references sys_user, which gets referenced by sys_user_grmember. Essentially, I want to select group "A", and then only show users which are in group "A" and have the sn_customerservice_agent role.

This feels like there probably is an easy solution that I'm just not seeing - any help is greatly appreciated.

Thanks,
Max

[Jaspal Singh]

Hi Max,

You need to create create a database view for 

sys_user_has_role

&

sys_user_grmember 

table & then report on the database view created table.

[Philippe Casidy · Accepted solution]

Hi Max,

If that helps, sys_user has the intrinsic characteristic of being queriable on "Roles"

All > Roles = sn_customerservice_agent

Hope that helps.

Philippe

[Max Nowak]

Thanks Philippe! I actually didn't know that, this means I'll be able to just use sys_user_grmember as base table, and  dotwalk to user.roles to ensure that I only list people with a specific role.

Edit: Or even better, just use the sys_user table as base and use the related list conditions in the report configuration to filter for a specific group.

That's a much better solution than creating a database view, thanks again!