5 Replies Latest reply on Jun 25, 2019 4:03 AM by Shreyas Swamy

    Smart Reporting - SLA details

    Sam Cross
      Share This:

      Hi,

       

      I currently have a report on all our top priority calls that are open. The reports runs on

      Priority

      Incident/Work Order hyperlink

      Submit Date

      Status

      SLM Status

      Support Group

      Assignee

      Customer Name

      Summary

       

      We would like more detail around the SLA's - SLM Status only details whether it is within target, warning or breached. We would like to see exactly when it is due to breach in this report, at a minimum really, we would like percentage etc. 

       

      I've tried creating a report in Service Level Management but it doesn't look at work orders too.

      I've also tried creating a sub query type, but I cant seem to get it to work.

       

      Any tips from anyone?

        • 1. Re: Smart Reporting - SLA details
          Shreyas Swamy

          'SLM:Measurement' form holds most of the data points you mentioned in your query.

          Have you considered joining this form to your view?

          • 2. Re: Smart Reporting - SLA details
            Sam Cross

            Sounds like it but I cant work out how to add it to an Incident Management report with a Union Sub Query with Work Order.

             

            I have a Service Level Management report which I set up as a test and I can roughly get the information I need (I cant seem to get a useful Customer name or Summary field) but I cant find 'SLM:Measurement'  to add it to that one either.

            • 3. Re: Smart Reporting - SLA details
              Shreyas Swamy

              Can you provide the SQL generated by the report? It will help understand the structure and logic better.

              • 4. Re: Smart Reporting - SLA details
                Sam Cross

                SELECT DISTINCT

                  `HPD:Help Desk`.`Priority`,

                  `HPD:Help Desk`.`Incident Number`,

                  `HPD:Help Desk`.`Submit Date`,

                  `HPD:Help Desk`.`Status`,

                  `HPD:Help Desk`.`SLM Status`,

                  `HPD:Help Desk`.`Assigned Group`,

                  `HPD:Help Desk`.`Assignee`,

                  `HPD:Help Desk`.`First Name` + ' ' + `HPD:Help Desk`.`Last Name`,

                  `HPD:Help Desk`.`Description`

                FROM `AR System Schema`.`HPD:Help Desk`

                WHERE (

                  `HPD:Help Desk`.`Priority` IN ('Critical', 'High')

                  AND `HPD:Help Desk`.`Status` IN ('New', 'Assigned', 'In Progress', 'Pending')

                )

                UNION

                SELECT DISTINCT

                  `WOI:WorkOrder`.`Priority`,

                  `WOI:WorkOrder`.`Work Order ID`,

                  `WOI:WorkInfo`.`Submit Date`,

                  `WOI:WorkOrder`.`Status`,

                  `WOI:WorkOrder`.`SLM Status`,

                  `WOI:WorkOrder`.`ASGRP`,

                  `WOI:WorkOrder`.`Request Assignee`,

                  `WOI:WorkOrder`.`Customer First Name` + ' ' + `WOI:WorkOrder`.`Customer Last Name`,

                  `WOI:WorkOrder`.`Summary`

                FROM `AR System Schema`.`WOI:WorkOrder`

                LEFT OUTER JOIN `AR System Schema`.`WOI:WorkInfo`

                ON (

                  `WOI:WorkOrder`.`Work Order ID` = `WOI:WorkInfo`.`Work Order ID`

                )

                WHERE (

                  `WOI:WorkOrder`.`Priority` IN ('High', 'Critical')

                  AND `WOI:WorkOrder`.`Status` IN ('Assigned', 'In Progress', 'Pending', 'Waiting Approval')

                )

                • 5. Re: Smart Reporting - SLA details
                  Shreyas Swamy

                  Ok, so for the Incident query, customize the Incident Management View by adding form SLM:Measurement and joining Incident ID on Help Desk with ApplicationUserFriendlyID on SLM:Measurement form and then pull in all the fields you need.

                  Now for the Work Order query, if you have SLM configured for the same, it should be storing SLM details on the same form SLM:Measurement, so use the same join on the Work Order View.

                  If it is not required on Work Order View, you could use dummy columns in the sub query.