Home General Discussion

SR Reporting Question

Kirsty_EndeKirsty_Ende Customer IT Monkey ✭

 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! :|


  • Options
    Geoff_RossGeoff_Ross Cireson Consultant O.G.
    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?

  • Options
    Kirsty_EndeKirsty_Ende Customer IT Monkey ✭

    Thanks for replying.

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


  • Options
    Geoff_RossGeoff_Ross Cireson Consultant O.G.
    OK, understood. We're here to help. Let me dig out some resources and post here shortly.
  • Options
    Geoff_RossGeoff_Ross Cireson Consultant O.G.
  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    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.
  • Options
    Kirsty_EndeKirsty_Ende Customer IT Monkey ✭

    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 :)

Sign In or Register to comment.