Report showing full assignment group history across a row for a group of Incidents

[mullenr]

I am in need of a report or creating a report (Washington Release) that shows Assignment Group History (including groups that may have touched an incident multiple times during it's lifecycle.  The Report format would need to be something like:

Number                   Reassignment Count       Assign Grp 1    Assign Grp 2       Assign Grp 3     Assign Grp 4 .....     .......   ......

INC0001234                        4                          ABC123 grp     EFG456 grp        RST123 grp      ABC123 grp     

INC0007845                        2                          RST123 grp      ABC123 grp

INC9876543                        5                          QRS666 grp     ABC123 grp      QRS666 grp      EFG456 grp    XYZ2222 

...........

........... 

[Mark Manders]

The only way to get the 'report' like you have it in your question, is to completely customize it and even then it will be a hassle to get it as report.

You could try to utilize Robert's solution and create a scheduled job with script to create a table with this info from the metrics and let it email the result (or just trigger an event to send an email and do add the logic in an mail script to get your table). 

The question is, like with anything having to do with reports, what is the added value of this report? You already have the reassignment count. You can utilize the metrics to see duration of an 'over-assigned' ticket or even create SLA Definitions if there are some other business needs. 

I've been around for a while and often these kinds of requirements come from people that have seen such a report somewhere and want it now as well. You are using ServiceNow and that does not provide you these kinds of reports OOB. 

[Uncle Rob]

A quick note on WHY this kind of report hasn't been created.
How many columns would it need?  Answer:  at least one column for the max number of assignments you've ever had.
So if you had one incident with 40 reassignments, ALL rows have 40 entries, even if the vast majority only have 3 or 4.

I also shudder to imagine the query, because for each row you'd have a subquery that went at either Metric Instance or sys_audit.  That subquery would have to sort, then write to each of the columns.  I saw "write to" because reports are only a view of existing data.  This solution would HAVE to write the data somewhere if only to view it this way.

When I find myself in these "question makes sense but solution doesn't exist on platform", I start digging deeper on the request.  What is the outcome they're trying to achieve.   "Reduce re-assignments"?  Ok, there's MANY ways to do that that aren't necessarily a list of assignment sequences for every incident... consider:

GROUPS INVOLVED IN BOUNCING
A report on Metric Instance (Assignment Group definition) where Reassignment Count >= 3
Bar chart, grouped by Assignment Group.  This will show you groups involved in reassignments.

BOUNCING GROUP TIME CONSUMPTION
By summing duration (mi_duration) you can get a feel for what groups "keep" the bounces the longest.
Notice how these two graphs together tell an interesting story.  The Blue group receives the most bounces and keeps them the longest (by a longshot!)

If you have the right license, you can also look into the Process Mining app which visualizes all that history for you.