Reporting on users with roles in groups

[stephenrodgers]

Hi folks,

I'm trying to make a list report that gets at user data (name, manager, last login, etc.), their roles (or at least the ability to exclude users that have no roles), and assignment group data (pretty much just group name and group manager). Ideally it would be something like the following:

• List Report
• Include fields: Assignment group name, user name, assignment group.manager, user.manager <<< Easy if I can get at User data
• Filter 1: Last Login is <WHATEVER> (empty, more than 90 days ago, etc.) <<< Easy if I can get at User data
• Filter 2: Only show users who have at least one role <<< Easy if I can get at sys_user_has_role table

When I try to see what tables might work, I get the following:

• sys_user gets me user data, but not groups or roles
• sys_user_group gets me group data, but not users or roles
• sys_group_has_role gets me group and role data, but not users
• sys_user_has_role gets me role data and lets me dotwalk to user data, but not groups
• sys_user_grmember gets me group data lets me dotwalk to user data, but not roles

Is there some clever way to do this that I'm just not seeing? It seems like the best I can do is 2 out of 3. Thanks!

[Chuck Tomasi]

Hi Stephen,

Currently, you need to use Database Views to do the joins on these tables to make one virtual table that you can then report against. This is a pretty complex one and I'm not going to suggest that I know the ultimate solution to get what you want.

The good news is that our teams are working on an easier way to do this type of common reporting request. No ETA yet, but it is in the works for a future release.

[stephenrodgers]

Thanks Chuck!

[ganesh2310_kuma]

Hi Chuck,

Could you please help me on the same scenario. Please let me know how to generate the report on this?

thanks,

Ganesh

[Paul Rice · Accepted solution]

Take a look at the database view called, "matching_user_group_skills".

All the data you need should be in there.

It joins:

sys_user

sys_user_grmember

sys_user_has_skill

user_task_stats

sys_user_has_role

Data will be denormalized.   You do get one row per user, group, role, skill combination so some creative filtering might be needed for your report.

Let me know if that helps.

PTR