2 Replies Latest reply on Jul 5, 2016 5:30 AM by mkelbie

    Report on all modules or(Request/Incident)

    mkelbie Rookie

      I am looking to create a view in SQL that will cater for all modules in a monthly statement/report.

       

      I have built some queries in the console which provide a lot of the information users need but I am struggling to decide what is the best method of reporting on all tickets from Crystal and if I need to keep them separate like the queries/dashbaords. Ideally I would like 1 report that could be run per customer/month that can provide everything they need.

       

      The report I am currently working on is a monthly statement to list the following;

       

      1. Received
      2. Current Open
      3. Closed (Resolved) - This is where I am struggling, I want resolved/fulfilled, rejected etc (anything that has been dealt with)
      4. Breached or any other specific requirements I might get like that.

       

      I can produce the above for Incidents using

       

      LEFT JOIN im_incident_resolution ir ON ir.im_incident_guid = p.pm_guid

       

      This provides me with a resolution date in the view so I can use that but I am not sure how I can bring in requests? We are looking at a new process that records a date in the process table for anything that is "dealt" with but this will need to go through CAB/QA etc I can't see me being the only one who wants to report on everything.

       

       

      We are only just starting to roll out request now incident is setup but eventually we will add change, problem etc so it would be nice if I can have a "master view" and 1 monthly statement of activity regardless of the module.

       

       

       

      FROM pm_process P

       

        LEFT JOIN im_incident i ON i.pm_guid = p.pm_guid

        LEFT JOIN rm_request r on r.pm_guid = p.pm_guid

        • 1. Re: Report on all modules or(Request/Incident)
          Waldy Employee

          Hi there,

                      you can use a UNION in a SQL view to combine multiple selects:

           

          
          
          SELECT 'Incident' [Type], pm_id [Number], pm_creation_date [Creation Date], lc_title [Status], 
           CASE WHEN im_is_breached = 1 THEN 'Yes' ELSE 'No' END [Breached?], im_creation_date [Resolution Date]
          FROM pm_process p INNER JOIN im_incident i ON i.pm_guid = p.pm_guid
          INNER JOIN lc_status s ON s.lc_guid = p.pm_status_guid
          LEFT JOIN im_incident_resolution ir ON ir.im_incident_guid = i.pm_guid 
          UNION
          SELECT 'Request', pm_id, pm_creation_date, lc_title, CASE WHEN rm_is_breached = 1 THEN 'Yes' ELSE 'No' END, rm_creation_date 
          FROM pm_process p INNER JOIN rm_request r ON r.pm_guid = p.pm_guid
          INNER JOIN lc_status s ON s.lc_guid = p.pm_status_guid
          LEFT JOIN rm_request_resolution rr ON rr.rm_request_guid = r.pm_guid
          
          • 2. Re: Report on all modules or(Request/Incident)
            mkelbie Rookie

            Thanks Waldy,

             

            will keep that for future reference, I have chosen to create a view for "all received" and then resolved/closed I am keeping separate just to get a report done. I will have a play with your SQL and see if I can make improvements to report.

             

             

            Thanks