How would you know the amount of columns to put on? Since a ticket could be re-assigned a theoretically infinite amount of times? And what drives the need to see it in this form specifically?
Whenever I was trying to tell the story of bad assignments, I'd simply report on the Incident_Metric table where definition = Assignment Group. That's a log of every time the group changes, and who did it. You can get the same story, you just don't need to spend 1000 hours creating a custom solution on the platform.
The incident_metric table joins Incidents and metric_instances. Its VERY important that you look at the sorts carefully.
Add sort: inc_number (the incident's number)
Add sort: mi_sys_created_on (the created date of the metric instance record)
Make sure your list view shows the Created (mi_sys_created_on) and not any of the other created fields.
1. Any insight into how to create the incident_metric table? (We currently have the metric_instance table and incident table but not incident_metric)
Hey @ChristinB - go to your navigator bar and type in incident_metric.list.
Its an OOB database view that isn't found on the navigator bar, so you have to manually nav to it.
1a. Is it possible to do the same for sc_task table as well?
VERY Possible but not OOB. Before you have a DBView joining metric_instance to <whatever table you want>, you must first have Metric Definitions [metric_definition] creating Metric Instances. Here's the OOB definitions grouped by table. Doesn't look like there are any sc_task ones defined. Good news is you'll get practice.
Its easy to define the Database Views since you'll have incident_metric as an example
2. Will it count toward our custom table count?
No, because its not a table, its a database view. A database view allows you to join tables together and view the results. It only LOOKS like a table. This will be true even if you create your own sc_task_metrics DBView (after you build sc_task metrics mind you!)
3. Will it run retroactively? (meaning we'll be able to report on incidents that have been reassigned to different groups over all time or just for group reassignments going forward?)
Practically? No. A metric definition does TWO things. It creates a new metric_instance record on a field value change its been told to watch. But it ALSO ends a previous one if it exists. Maybe someone has figured out how to script against the audit history to build the metric instances but I have to be honest... that sounds risky.
The good news is (at least for incident) that there's a variety of SUPER handy metric definitions OOB.
If you want the same ones for sc_task, just replicate them and enjoy more powerful reporting going forward.
