How to create a report/dashboard to view groups and roles associated with module/application

[MeghanaJ]

We need a report created so that the platform team can quickly view what groups are associated with a module and the roles that group has. This will be easy for them to request correct access based on the job function of the requestor 

[James Chun]

Hi @MeghanaJ,

What do you mean by 'what groups are associated with a module'?

In regards to the second part of the question (roles that group has), you can create a simple list report on the [sys_group_has_role] table like the below:

Cheers

[MeghanaJ]

Thanks!! However one of the team wants a report that would help them add people/new hires to appropriate groups based on their job duties. So what I mean by the module is: If someone is requesting access to say demand Module the report would show which groups have that particular role

[HrishabhKumar]

Hi @MeghanaJ ,

Try the below method by creating database view:

Step 1: Create a Database View

Combine relevant data from the sys_user_group, sys_user_has_role, sys_user_role, and your module table.

1. Navigate to Database Views:

   • Go to System Definition > Database Views.
   • Click New and name it GroupsRolesModulesView.

2. Define Relationships: Add and relate the tables using appropriate join conditions. Example SQL:

SELECT g.name AS group_name, r.name AS role_name, m.name AS module_name FROM sys_user_group g JOIN sys_user_has_role hr ON hr.user = g.sys_id JOIN sys_user_role r ON r.sys_id = hr.role JOIN some_module_table m ON m.group = g.sys_id WHERE m.name IS NOT NULL

Adjust table and column names based on your instance's schema.

Step 2: Create the Report

1. Navigate to Reports:

   • Go to Reports > Create New.

2. Define the Report:

   • Name: "Group Roles and Modules Report"
   • Type: List
   • Data Source: GroupsRolesModulesView

3. Configure Columns:

   • Select columns: group_name, role_name, module_name.

4. Save and Share:

   • Save the report.
   • Use the Share option to grant access to the platform team.
   • Optionally, schedule the report for regular distribution.

Thanks,

Hope this helps.

If my response proves helpful please mark it helpful and accept it as solution to close this thread.

[MeghanaJ]

Hi @HrishabhKumar,

Created a database view like you suggested. However when I click on the 'Try it' option on the DB view nothing show up. Here is the query that was used: 

SELECT g.name, r.name, m.name FROM g JOIN hr ON hr.user = g.sys_id JOIN r ON r.sys_id = hr.role JOIN m ON m.group = g.sys_id WHERE m.name IS NOT NULL

Can you let me know what is missing or wrong?

Thanks