Reporting Question: Collect a report that has number of assigned to changes and what routes?

[JLWhelan]

We have a General Incident form that is used almost exclusively by our user population and our tier 1 support teams handle routing to the appropriate teams. 

We're looking to create a report based on 1.) the number of groups that incidents are assigned to and 2.) when the assignment returns to our tier 1 support queue and moves to another group. Purpose for this is to determine what our assignment templates should be adjusted to to remove additional downtime on incidents.

Since there is a "Field changes" activity that lists "Assigned to" and "Assigned Group" in the activity stream, I feel like this is a possibility, but we're at an impasse on where to start. 

Additionally, we'll also be looking to use this report to confirm which analysts need additional training, so I'll be looking for who changed the field, if that's possible. 

Thank you for your time, 

[JP - Kyndryl · Accepted solution]

Hi JL,

I would try a multi-level pivot table report on the incident_metric database view with Definition = Assignment Group (this is OOTB).

Column:  Value (which contains the Assignment Group)

Row:  inc_number, mi_sys_created_on

The Count column (on a Total row) will give you the total number of group assignments.

If multiple lines target the same Incident number for the Service Desk group, it means the incident returned to the Service Desk at some point.

If you want to see the duration in each group, replace the Aggregation = Count, by Aggregation = Average and select the Duration field.