I have achieved this, the key to this is to use the tps_audit_trail table.
Key attributes of the tps_audit_trail table
- tps_object_guid = the GUID of the Incident/Change/Problem will link to either pm_process or im_incident on pm_guid field.
- tps_function_id = the GUID of the Business Function invoked, will link to md_business_function on md_guid, the md_name/md_title will then show what action was invoked to create this audit trail record.
- tps_creation_datetime = the date/time of the action occuring.
- tps_is_automatic_action = flag to indicate whether the action was a manual action (value = 0) or automatic action (value = 1).
- tps_value_object_guid = the GUID of the object that was created, e.g. if the action was "Resolve" then this will be the value of a record in the im_incident_resolution table in the field im_guid.
Create a report linking Incident/Process to tps_audit_trail, sort the records by the tps_creation_datetime field.
Create a sub-report for Resolutions passing in the tps_value_object_guid as the value to the im_guid field, suppress the sub-report if the record is not a "Resolve" action.
Create another section in the Details section, and create another sub-report for Notes passing in the tps_value_object_guid as the value to the im_guid field, suppress the sub-report if the record is not a "AddNote" action.
Repeat for each action you wish to report on.
Suppress all other actions.
It may be worth adding an index onto the tps_audit_trail table on the tps_object_guid field in order to improve the speed of the query, as the tps_audit_trail table becomes very large over time.
That's a very clever approach. I wouldn't have thought of using the audit trail and supression of the sub-reports is a great way of getting the information to display in the correct order.
Do you have a copy of that report you could share with all of us?