3 Replies Latest reply on Sep 1, 2015 1:57 PM by eric.wagner

    Incident.rpt to show notes, with customer, etc. in chronological order

    Rookie

      Hello,

       

      Has anyone created a version of the Incident.rpt that will show all of the notes, with customer, with third party, resolutions, etc. together in chronological order?

       

      I can get the data on the same report by creating sub-reports but this does not show the information in the right order, rather all of the notes, then the with customer, and so on.   This can be confusing when printing an Incident and trying to read through the timeline.

       

      Has anyone achived this?

       

      Thanks,

      Lee

        • 1. Re: Incident.rpt to show notes, with customer, etc. in chronological order
          JJJ Employee

          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.

          • 2. Re: Incident.rpt to show notes, with customer, etc. in chronological order
            Rookie

            Thanks JJJ,

             

            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.

             

            Cheers....

            Lee

            • 3. Re: Incident.rpt to show notes, with customer, etc. in chronological order
              eric.wagner Apprentice

              Do you have a copy of that report you could share with all of us?