Need help to build a report by Incident History.

Community Alums · ‎09-26-2017

I need to build a report on Incident history data. When I was looking at the "sys_audit" table, it was huge and when applied filters it was taking so much time and finally transaction failed.

One ticket worked by different assignment groups and their time stamps,.

Need best ideas to build something like the screen shot.

Thanks,

Rajini

Uncle Rob · ‎09-26-2017

Go to your Metric Definitions Module

Check to see if "Assignment Group" on Incident table is active (it should be by default).

That'll have a related list showing you all the times an Assignment Group has changed, what date the change happened, and the user who made the transaction.

Assuming its active, you can also build a report on the "Incident Metrics" table (really a database view that's been created for this purpose since way back when).
Add a condition to a report on the Incident Metrics table where Definition = "Assignment Group" (but since there's possibly multiple, pick the one for the Incident table)

View solution in original post

Uncle Rob · ‎09-26-2017

Very very important lesson here for taking the long view on platform architecture.

We should never ever report on the history table. I mean... if the *LEGAL* department asks, maybe... but in all other cases design should have been considered first.

Take a step back, look at the fields where stakeholders plausibly would report transactions over time (most of them are already covered in the Metric Definition table).

Aside from just tracking the date, value, and person who made the change, it can also track durations... giving you neat little windows into times in each state, assignment group, assign to, etc.

Community Alums · ‎09-26-2017

Thank you Robert. Just applied your solution, it worked great exactly as we wanted it..

Rajini