FTE Counts based on users in specific groups

[Brian Lancaster]

I'm creating a Dashboard that on a monthly basis gets the count of all tasks type we use (incident, change task, catalog task, problem task, etc.). It then combines them into one count. Now they want to add the count of employees. This is all based on groups that have a cost center. I'm not sure what the best way to count the employees or be able to get historical data. Has anybody ever done anything like this before?

[Krzysz]

Hi @Brian Lancaster, have you considered building an indicator on the sys_user table? You should be able to access/dot-walk to a field that can identify somebody as an employee.

However, not sure if that would help with the historical data

[Amit Gujarathi]

HI @Brian Lancaster ,
I trust you are doing great.

you can follow these steps:

1. Task Type Count:

   • Create a scripted report in ServiceNow to fetch the count of tasks for each type (incident, change task, catalog task, problem task, etc.) on a monthly basis.
   • Utilize the GlideAggregate API to aggregate the task records and calculate the count for each task type.
   • Store the monthly task counts in a dedicated table or field in ServiceNow for historical data tracking.

2. Employee Count:

   • Identify the source of employee data in your company's infrastructure (such as HR system, Active Directory, or a custom employee management application).
   • Establish an integration or data import mechanism to regularly synchronize the employee data with ServiceNow.
   • Create a script or business rule in ServiceNow to calculate the count of employees associated with each cost center.
   • Store the employee counts in a dedicated table or field in ServiceNow for historical tracking.

3. Combining Task and Employee Counts:

   • Create a dashboard in ServiceNow, utilizing Performance Analytics or other reporting tools, to visualize the combined count of tasks and employees.
   • Use appropriate chart types (bar charts, pie charts, etc.) to represent the data effectively.
   • Incorporate filters or selectors to allow users to view historical data for specific time periods or cost centers.

4. Historical Data:

   • Ensure that the task and employee counts are stored and associated with specific dates or time periods.
   • Design your reporting solution to allow users to select a desired time range to view historical data.
   • Leverage ServiceNow's reporting capabilities or custom scripts to fetch and display the historical data based on user selection.

[Brian Lancaster]

I think I got most of it worked out. I use sys_user_grmember as my indicator source. This way I could use cost center to know which department in IT the employee was in. Then for my automated indicator does a distinct count on the user field. The main problem is that I cannot get historical data from the sys_user_grmember group because when a user is removed from a group they are deleted. The other thing that may cause an issue is that we started using Agile Development for a project. That group cannot have a cost center associated with it because people from multiple cost centers are associated to the group.