Custom Report

Richard_Byers

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!

Peter_Miklian

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_Byers

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_Dzyacky

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_Blease

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_Dzyacky

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

Oh yea or that!

Richard_Byers

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!