none
Report: Analyst Performance & SLA Breached Togather

    Question

  • Hi Everyone,

    I have a query to generate report Incidents analyst performance report and SLA Breached report togather. Is this possible by sql report builder 3.0. If i generate this reports seperately it works well. but i dont understand how to merged both reports togather, regarding to this my query is;

    Select Count(I.Id),U.Displayname
    From I
    Full JOIN WIDimvw
     ON I.EntityDimKey = WIDimvw.EntityDimKey
      JOIN WIATUFactvw
     ON WIDimvw.WorkItemDimKey = WIATUFactvw.WorkItemDimKey
      JOIN U
     ON WIATUFactvw.WorkItemAssignedToUser_U = U.UserDimKey

    this query is used to generate Incident Analyst Performance.
    And for SLA Breached Report just add a single where clause in the above query like this.

    Select Count(I.Id),U.Displayname
    From I
    Full JOIN WIDimvw
     ON I.EntityDimKey = WIDimvw.EntityDimKey
      JOIN WIATUFactvw
     ON WIDimvw.WorkItemDimKey = WIATUFactvw.WorkItemDimKey
      JOIN U
     ON WIATUFactvw.WorkItemAssignedToUser_U = U.UserDimKey
    Where I.ResolvedDate >= I.TargetResolutionTime

    So as I used the Where clause both reports affected.
    Kindly give me some suggestion that it is possible or not.

    Assigned   To: Assined Incident: SLA Breached Count:
    Fahad Ali Syed [ASyO] 24 8
    Fahad Mustafa [BCI Engr] 149 69
    Kashan Zaman [BCI Engr] 294 141
    Nasim Bano [AAO] 1 0
    Grand Total 17578 4637

    Tuesday, May 14, 2013 11:37 AM

Answers

  • Hi,

    I would say there are two ways you can approach this, either UNION the query together, or add a column if the SLA has been breached. I would say the second option is a little cleaner SQL code:

    Select Count(I.Id),U.Displayname
    
    (CASE WHEN I.ResolvedDate >= I.TargetResolutionTime THEN 1 ELSE 0 END) AS SLABreach
    
    From I
    Full JOIN WIDimvw
     ON I.EntityDimKey = WIDimvw.EntityDimKey
      JOIN WIATUFactvw
     ON WIDimvw.WorkItemDimKey = WIATUFactvw.WorkItemDimKey
      JOIN U
     ON WIATUFactvw.WorkItemAssignedToUser_U = U.UserDimKey

    This gives you an extra column with a 1 if the SLA has been breached and a 0 if not. Simply sum it up in your report and you're done!


    -

    • Proposed as answer by Erik Monchen Wednesday, May 15, 2013 7:16 AM
    • Marked as answer by Ahsan Misbah Wednesday, May 15, 2013 12:34 PM
    Tuesday, May 14, 2013 12:02 PM