Home Service Manager

Custom Report

Richard_ByersRichard_Byers Customer IT Monkey ✭

Has anyone developed a report (perhaps from Data Warehouse) that will capture all of the activities of a particular Service Request?

Our Management wants to analyse where the bottlenecks are in long running Service Requests. Rather than doing a deep dive, I thought I might asked if someone had already developed something like that.

Thanks!

Answers

  • Peter_MiklianPeter_Miklian Customer Advanced IT Monkey ✭✭✭

    We were thinking about such report, too. Taking activity duration =( activity actual end date) - (activity actual start date) combining with Activity Implementer we could identify activities and analysts slowing processes and SR fulfilment.

    We are pulling data from ServiceManagement database to QlikView server for easy reporting. This would be nice place for working with this data in our company, too.

    However, we didn't work on this yet.

  • Richard_ByersRichard_Byers Customer IT Monkey ✭

    I think the activities are in ActivityDimvw and the Service Requests are in ServiceRequestDimvw but I'm having issues linking those tables.

    I started with:

    Select 

    Coalesce(ROInfo.DisplayName, 'Other Sources') as [RequestOffering], 

    SR.Id, 

    SR.Title, 

    SR.CreatedDate, 

    SR.CompletedDate, 

    datediff(day, SR.CreatedDate, SR.CompletedDate) as [DaysOpen] from ServiceRequestDimvw SR 

    left join WorkItemDimvw WI on SR.EntityDimKey = WI.EntityDimKey

    left join WorkItemRelatesToRequestOfferingFactvw WIRO on WI.WorkItemDimKey = WIRO.WorkItemDimKey

    left join RequestOfferingDimvw ROInfo on WIRO.WorkItemRelatesToRequestOffering_RequestOfferingDimKey = ROInfo.RequestOfferingDimKey

    where SR.CompletedDate >= '3/1/2020' and SR.CompletedDate < '04/1/2020' and SR.CompletedDate is not NULL

  • Adam_DzyackyAdam_Dzyacky Customer Contributor Monkey ✭✭✭✭✭
    edited April 2

    I feel like I have something like this for the DW. Need to do some looking but I can tell you the joins that you're looking for are -

    FROM Servicerequestdimvw as sq
    	INNER JOIN WorkItemDimvw as WI on sq.EntityDimKey = WI.EntityDimKey
    	INNER JOIN WorkItemContainsActivityFactvw as WICA on WICA.WorkItemDimKey=WI.WorkItemDimKey
    	INNER JOIN ActivityDimvw as ad on ad.ActivityDimKey=WICA.WorkItemContainsActivity_ActivityDimKey
    	INNER JOIN ActivityStatusvw as outriggerTwo ON ad.Status_ActivityStatusId=outriggerTwo.ActivityStatusId
    

    If my memory serves correct, I also recall doing a number of sub queries to get nested Activities though.

  • Dijon_BleaseDijon_Blease Customer IT Monkey ✭

    You probably want to use something like a recursive CTE to get the nested activities. Here is an old example I found on getting all child activities of a SR:

    USE [DWDataMart]
    
    
    DECLARE @WI_ID VARCHAR(255) = 'SR12345'
    ;WITH 
    	sr_table 
    		AS ( select sr.id Workitem, a.Id Child, 0 'Level'
    			from WorkItemDimvw sr
    			INNER JOIN WorkItemContainsActivityFactvw wifact on wifact.WorkItemDimKey = sr.WorkItemDimKey
    			INNER JOIN ActivityDimvw A ON wifact.WorkItemContainsActivity_ActivityDimKey = A.ActivityDimKey
    			where sr.Id = @WI_ID
    
    
    			UNION ALL 
    
    
    			Select c.Id Workitem, a1.Id Child, el.Level + 1
    			FROM WorkItemDimvw c
    			INNER JOIN WorkItemContainsActivityFactvw wifact on wifact.WorkItemDimKey = c.WorkItemDimKey
    			INNER JOIN ActivityDimvw A1 ON wifact.WorkItemContainsActivity_ActivityDimKey = A1.ActivityDimKey
    			INNER JOIN sr_table AS EL ON c.Id = el.Child
    		
    			)
    SELECT * 
    FROM sr_table
    
  • Adam_DzyackyAdam_Dzyacky Customer Contributor Monkey ✭✭✭✭✭

    You probably want to use something like a recursive CTE to get the nested activities

    Oh yea or that!

  • Richard_ByersRichard_Byers Customer IT Monkey ✭

    Thank you!! I really appreciate the responses!

    I tried this, the few SR's that I ran it against didn't return all of the activities in the Service Request and It seemed it ignore the Parallel Activity (and the activities inside) and didn't sequence the items like they were in the SR. However, it's a great start!

    Once requirement would be to select on the request name so that I get all of the Service Requests, in a given time frame, with the actual start and end dates so I can calculate an average for the time period (an average for all the first activity, an average for the second activity, etc.) If I can get the "assigned to" user, that would be great too!

Sign In or Register to comment.