Hi @Sheetal18 ,
To generate a report listing assignment groups and their respective information, including the number of people in the group, the number of people with the KM role, and the number of knowledge articles assigned to the group, you can use the following script:
CREATE VIEW assignment_group_report AS
SELECT
grp.name AS assignment_group,
COUNT(DISTINCT gm.user) AS total_people,
COUNT(DISTINCT CASE WHEN u.roles LIKE '%knowledge_manager%' THEN gm.user END) AS km_role_people,
COUNT(DISTINCT kb.assignment_group) AS total_articles
FROM
sys_user_group grp
LEFT JOIN sys_user_grmember gm ON gm.group = grp.sys_id
LEFT JOIN sys_user u ON u.sys_id = gm.user
LEFT JOIN kb_knowledge_base kb ON kb.assignment_group = grp.sys_id AND kb.workflow_state = 'published'
WHERE
grp.type = 'assignment_group'
GROUP BY
grp.sys_id, grp.name;
OR, you can create a database view that includes the necessary tables and fields mentioned in the code. Then, you can use that database view as a joined table in the Report tool in ServiceNow to generate the desired report.
Here's an example of how you can create a database view using the tables and fields mentioned in the previous code:
CREATE VIEW assignment_group_report AS
SELECT
grp.name AS assignment_group,
COUNT(DISTINCT gm.user) AS total_people,
COUNT(DISTINCT CASE WHEN u.roles LIKE '%knowledge_manager%' THEN gm.user END) AS km_role_people,
COUNT(DISTINCT kb.assignment_group) AS total_articles
FROM
sys_user_group grp
LEFT JOIN sys_user_grmember gm ON gm.group = grp.sys_id
LEFT JOIN sys_user u ON u.sys_id = gm.user
LEFT JOIN kb_knowledge_base kb ON kb.assignment_group = grp.sys_id AND kb.workflow_state = 'published'
WHERE
grp.type = 'assignment_group'
GROUP BY
grp.sys_id, grp.name;
Once you have created the database view, you can use it in the Report tool in ServiceNow by following these steps:
- Navigate to the ServiceNow application menu.
- Search for "Reports" and select "Reports" to open the Report Designer.
- Click the "New" button to create a new report.
- Configure the report settings such as the report title, description, and type.
- In the "Data" tab, select "Join Query" as the data source.
- In the "Tables" section, select the "assignment_group_report" database view that you created.
- Configure the required fields and conditions for the report based on your needs.
- Customize the report layout, grouping, and sorting options as desired.
- Save the report and run it to generate the desired output.
Also, refer Database view creation
If my response was helpful in resolving the issue, please consider accepting it as a solution by clicking on the ✅Accept solution button and giving it a thumbs up 👍. This will benefit others who may have a similar question in the future.
Thank you!
Ratnakar
