Home› Service Manager
Options

Custom Report

Richard_ByersRichard_Byers Customer IT Monkey ✭
in Service Manager

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

  • Options
    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.

  • Options
    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

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited April 2020

    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.

  • Options
    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
  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

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

    Oh yea or that!

  • Options
    Richard_ByersRichard_Byers Customer IT Monkey ✭
    https://community.cireson.com/discussion/comment/20696#Comment_20696

    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.