11 Replies Latest reply on Sep 3, 2009 8:22 AM by blaine67

    Reporting on Incident Notes

    Rookie

      I am having trouble reporting on Incident Notes - I want to simply be able to print out a Note but don't seem to retrive any data on the crystal report Notes.rpt

        • 1. Re: Reporting on Incident Notes
          Lara Hellman SupportEmployee

          Hi Bill,

           

          Have you created a Note.rpt Crystal Report?  There isn't one by default, it's one you'll need to create for yourself.

           

          Thanks,

           

          Lara

          • 2. Re: Reporting on Incident Notes
            Rookie

            Hello Lara

             

            Yes I created a Crystal Report. I had attempted to attach it to my original post!

             

            Thanks

             

            Bill

            • 3. Re: Reporting on Incident Notes
              Rookie

              This report works fine Bill, are you sure your passing the the im_incident_note.im_guid to the parameter and not he im_incident.pm_guid?

               

              I changed the parameter type to number and changed the report selection to use the im_incident.im_id so that I could test with just an incident number as apposed to a guid. I'd upload it but I use crystal 2008 to edit!

               

              report selection criteria:

              {im_incident.im_id}= {?Guid}

               

              Change the  {?Guid}  type to number and run the report with an incident number.

              • 4. Re: Reporting on Incident Notes
                Lara Hellman SupportEmployee

                Hi Bill,

                 

                Apologies, I completely missed that.  I that case, have you added the report to the list of avaiable reports in the 'Crystal Reports' component?

                 

                Thanks,

                 

                Lara

                • 5. Re: Reporting on Incident Notes
                  Rookie

                  I think so  - the sql is showing as:

                   


                  SELECT "im_incident"."IM_ID", "im_incident_note"."IM_CREATION_DATE", "im_incident_note"."IM_TITLE", "im_incident_note"."IM_GUID", "im_incident_note"."IM_CATEGORY_GUID", "im_incident_note"."IM_LAST_UPDATE", "im_incident_note"."IM_CREATION_USER_GUID", "im_incident_note"."IM_LAST_UPDATE_USER_GUID", "im_incident_note"."IM_TEXT"
                  FROM   "ITBM_USER"."IM_INCIDENT" "im_incident" LEFT OUTER JOIN "ITBM_USER"."IM_INCIDENT_NOTE" "im_incident_note" ON "im_incident"."PM_GUID"="im_incident_note"."IM_INCIDENT_GUID"
                  WHERE  "im_incident_note"."IM_GUID"=0x7660
                  ORDER BY "im_incident"."IM_ID", "im_incident_note"."IM_GUID"

                  • 6. Re: Reporting on Incident Notes
                    Rookie

                    Hello Lara - I have added the report to the list of available reports yes.

                    • 7. Re: Reporting on Incident Notes
                      Rookie

                      Ok I have managed to retrieve the note data - I now need to convert numbers (guids) to peoples names!

                       

                      If anyone is interested the sql is below:

                       

                      SELECT "im_incident"."IM_ID", "im_incident_note"."IM_CREATION_DATE", "im_incident_note"."IM_TITLE", "im_incident_note"."IM_GUID", "im_incident_note"."IM_CATEGORY_GUID", "im_incident_note"."IM_LAST_UPDATE", "im_incident_note"."IM_CREATION_USER_GUID", "im_incident_note"."IM_LAST_UPDATE_USER_GUID", "im_incident"."PM_GUID", "im_incident_note"."IM_INCIDENT_GUID", "im_incident_note"."IM_TEXT"
                      FROM   "ITBM_USER"."IM_INCIDENT" "im_incident" LEFT OUTER JOIN "ITBM_USER"."IM_INCIDENT_NOTE" "im_incident_note" ON "im_incident"."PM_GUID"="im_incident_note"."IM_INCIDENT_GUID"
                      WHERE  "im_incident"."PM_GUID"="im_incident_note"."IM_INCIDENT_GUID"
                      ORDER BY "im_incident"."IM_ID", "im_incident_note"."IM_GUID"

                      • 8. Re: Reporting on Incident Notes
                        karenpeacock SupportEmployee

                        Hi Bill

                         

                        You should be able to do this by adding the tps_user table into the report and linking from the note table guid for the user (im_incident_note"."IM_CREATION_USER_GUID) to the tps_guid on the tps_user table.

                         

                        Karen

                        1 of 1 people found this helpful
                        • 9. Re: Reporting on Incident Notes
                          Rookie

                          Ok my report now pulls out all the data I want and looks ok BUT! when I select the notes relating to a specific incident in Touchpaper Console it prints out all the notes for every incident!!!!

                          I guess I only need the note relating to the selected inciden somehow.

                          • 10. Re: Reporting on Incident Notes
                            Rookie

                            Ian's post #3 enabled me to correct the final issue - I can now get the notes relating to the specific incident selected.

                            I have attached the final crystal report for anyone who may want something similar (it is oracle based).

                             

                            Thanks everyone!

                            • 11. Re: Reporting on Incident Notes
                              Apprentice

                              Hi Bill

                               

                              I tried your report, I updated the data source connection and permissions to our server, but the data comes up blank.

                               

                              Am I missing something ?

                               

                              I'm not the best when it comes to Crystal reports.

                               

                              Also, does this print all the Notes, or just the one your looking at ?