6 Replies Latest reply on May 13, 2009 8:58 AM by AllanRichards

    Assignment data

    Rookie

      Hello

       

      I need to create a report to show when and where an incident has been assign from it being opened to resolved/closed.  I am having problems identifying the fields that I need to use.

       

      I think pm_process_assignment.pm_serial_number represents each assignment, so as an incident is reassigned the serial number is increased by 1.  Is this correct?

       

      If so, where can I find information for the status details, the group it was assigned to, and the analyst if there is one for each of the serial numbers for an incident?  The basic output I need is:

       

      Incident Number             Serial                Date                 Status               Group               Analyst

      1001                             1                      01/05/2009        Open                QueueA           

      1001                             2                      01/05/2009        Pending            QueueA            AnalystA

      1001                             3                      02/05/2009        In Progress        QueueB            AnalystB

      1001                             4                      02/05/2009        Resolved           QueueB            AnalystB

       

      Is this possible, can anybody advise which tables to look for the data?

       

      Thanks

       

      Allan

        • 1. Re: Assignment data
          elizabethcombrink Employee

          I use this statement as the basis of the crystal report.  It asks for the Incident GUID, so you need to replace the guid with the actual incident number (im_id)

           


          SELECT

          "pm_process_assignment"."pm_serial_number" As SerialNumber,
          "im_incident"."im_id",

          "im_incident"."pm_guid" AS pm_guid,
          "pm_process_assignment"."pm_title" AS AssignmentSubject,
          "pm_process_assignment"."pm_description" AS AssignmentBody,
          "tps_assignment_status"."tps_title" AS AssignmentStatus,

          "tps_user"."tps_title" AS AssignTo,
          "tps_group"."tps_title" AS AssignGroup,

          "pm_process_assignment"."pm_creation_date" AS AssignDate,
          (select "tps_user"."tps_title" from tps_user
          where "tps_user"."tps_guid" = "pm_process_assignment"."pm_creation_user_guid") AS AssignBy,
          (select "tps_group"."tps_title" from tps_group
          where "tps_group"."tps_guid" = "pm_process_assignment"."pm_create_group_guid") AS AssignGroup


          FROM  
          ((("ITBM"."dbo"."im_incident" "im_incident"
          INNER JOIN "ITBM"."dbo"."pm_process_assignment" "pm_process_assignment" ON "im_incident"."pm_guid"="pm_process_assignment"."pm_process_guid")
          LEFT OUTER JOIN "ITBM"."dbo"."tps_user" "tps_user" ON "pm_process_assignment"."pm_user_guid"="tps_user"."tps_guid")
          LEFT OUTER JOIN "ITBM"."dbo"."tps_group" "tps_group" ON "pm_process_assignment"."pm_group_guid"="tps_group"."tps_guid")
          INNER JOIN "ITBM"."dbo"."tps_assignment_status" "tps_assignment_status" ON "pm_process_assignment"."pm_status_guid"="tps_assignment_status"."tps_guid"

           

          WHERE  "im_incident"."pm_guid"='{?pm_guid}'

           

          ORDER BY "pm_process_assignment"."pm_serial_number"

          1 of 1 people found this helpful
          • 2. Re: Assignment data
            Rookie

            Thank you for your help Elizabeth, I have almost got all of the data I need.  I have not used a SQL statement as the basis of the report as I have added the relevant tables using the DB Expert then selected the fields I require.

             

            I run the report and receive the required data.  The only bit of data I now need is the Assignment Status for which I am trying to use "tps_assignment_status"."tps_title" as in your statement.  The problem I have is that as soon as I add the "tps_assignment_status" table as a selected table to the report (so I can access the "tps_assignment_status"."tps_title" field) I no longer get an results output and the report is blank even though I use the same criteria.  Could this be a link problem or something else?

             

            Thanks

             

            Allan

            • 3. Re: Assignment data
              elizabethcombrink Employee

              It sounds like either you are linking the wrong guids together, or your link type is wrong.

              • 4. Re: Assignment data
                Lara Hellman SupportEmployee

                Hi Allan,

                 

                Which status is it that you want to display?  That of the Incident (as your example suggests) or that of the assignment (in which case all but the last one should be 'Completed')?

                 

                The actual Incident status can be derived by linking lc_status to pm_process.  However, in order to determine what this was historically you've got quite a lot of work on your hands.  I would suggest you take a look at the audit trail (tps_audit_trail, tps_audit_trail_value) to see if you're able to extract the status changes from there (assuming they're auditable in your database) and compare the timestamps to those of the timestmps on the creation of the assignments.  Alternatively you might want to investigate the feasibility of using a script to update a separate table with this data as it happens to avoid having to find substrings of the XML data in the audit tables in your reports.

                 

                Hope that helps to shed a little light for you....

                 

                Lara

                • 5. Re: Assignment data
                  Rookie

                  Hello Lara,

                   

                  Thank you for the information.  lc_status does have the required status value, however, it only brings back the last value for the incident as you said.  I looked at audit trail but that didn't seem to contain any values.

                   

                  Having used the code provided by Elizabeth as the basis of the report I found that pm_process_assignment.pm_title actual contains the historical data along with some other text but that should be easy to resolve.

                   

                  Thanks again

                   

                  Allan

                  • 6. Re: Assignment data
                    Rookie

                    Thank you Elizabeth, looking at your statement in more details I can actual get the data I need from pn_process_assignment.pm_title so I no longer need to link to the assignment_status table.

                     

                    Thanks again

                     

                    Allan