SR Reporting Question

Kirsty_Ende

 We have recently migrated to SCSM and have just been let loose on reporting cubes.

I am fine with IRs and have managed to get a wealth of data but having difficulty using the exported Service Catalogue cube to find:

Average time to complete (SR) by affected user department and by priority in October.

I should mention, I am exporting and manipulating the data in a Pivot Table and we have no SLOs built into the system at this time.

Can anyone help me please?

I'm usually quite good at just figuring this stuff out but am banging my head against the desk with this one!

Geoff_Ross

Hi @Kirsty_Ende

Sorry its taken you a while to get a response here. I guess you've stumped the community.

I know this isn't really answering your question, but this 'report' might be a lot easier to produce using Cireson Analytics. Is that something you have any experience with?

Geoff

Kirsty_Ende

Thanks for replying.

Not at all familiar. We are very new to this and the learning process is slow...

Kirsty

Geoff_Ross

OK, understood. We're here to help. Let me dig out some resources and post here shortly.

Geoff_Ross

Try this to kick you off
https://www.youtube.com/watch?v=Vq59Bj8PEHw

Adam_Dzyacky

While I can't provide the Cubes or CiresonAnalytics answer - I can do something in the way of the SCSM DW. As such, while this query doesn't feature Priority it does group by Affected User Department for October. But you can probably see how it can be altered.

DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = '10-01-2018'; 
SET @EndDate = '11-01-2018'; 

SELECT	count(sr.Id) as 'Service Requests', affectedUser.Department as 'Department', 
			avg(DATEDIFF(MINUTE, sr.CreatedDate, sr.CompletedDate)) / 60 + 
			avg(DATEDIFF(SECOND, sr.CreatedDate, sr.CompletedDate)) % 60 / 60.0 as [Hours]
FROM	UserDimvw as affectedUser
		INNER JOIN WorkItemAffectedUserFactvw ON affectedUser.UserDimKey = WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey 
		INNER JOIN WorkItemDim ON WorkItemAffectedUserFactvw.WorkItemDimKey = workitemdim.WorkItemDimKey
		INNER JOIN EntityDim as entity ON entity.EntityDimKey = WorkItemDim.EntityDimKey
		INNER JOIN ServiceRequestDim as sr ON entity.EntityDimKey = sr.EntityDimKey
WHERE	sr.CompletedDate is not null
		--and (affectedUser.Department like '%%')
		and ((sr.CreatedDate >= @StartDate) and (sr.CreatedDate < @EndDate + 1))
GROUP BY affectedUser.Department
ORDER BY [Service Requests] DESC

I've commented it out in the above SQL, but a filter that could easily be introduced here is filtering on the Affected User's department seen in the WHERE clause.

Kirsty_Ende

Analytics is exactly what we need so I have passed this information and webinar on to the powers that be to make a decision.

Thank you both for replying