Reporting on state changes in records

[gejoom]

Hye guys,

My business has a request to have a report which showcases all the HR Cases which have been in the 'awaiting for acceptance' state only once (Count = 1). I created a database view with the sn_hr_core_case table and the sys_audit table. After that I don't really see how this would be possible. You have columns like reassignment count, but that's of no use unfortunately.

[Jon Hogland]

Hi gejoom. You could utilize a GlideAggregate script for this, a few different ways. I'm sure there's a more graceful way, but bear with me: create an integer field beginning at 0, then create a client script that, onSubmit, if the state has changed from [insert state] to [Awaiting Acceptance], you increment the field +1. You could then report based on the state along with the integer field you created.

This may work better on a Business Rule, but I'm sure there are arguments for both solutions.

If you'd like a better explanation or need help creating this function, let me know. Otherwise, https://www.servicenow.com/docs/bundle/xanadu-api-reference/page/app-store/dev_portal/API_reference/... has all the references needed to utilize GlideAggregate. 

[Ankur Bawiskar]

@gejoom 

better to have a custom integer field on HR case table and increment it whenever the state changes to Awaiting for Acceptance

Then use this field for reporting.

OR

you can use metrics for this on HR case table

Refer the OOB "Incident state duration" and create something similar for HR case table and then use it in reporting

If my response helped please mark it correct and close the thread so that it benefits future readers.