Hi Casey Testing this query, and if you have an incident that has been assigned to several analysts, the report will display the incident more than once. Is it possible to set a filter to show only the most recent? (Last Updated)?
Thanks Peter Från: [email protected] [mailto:[email protected]] För Robertson, Casey Skickat: den 13 juni 2013 01:36 Till: [email protected] Ämne: [servman] Helpful SQL queries #2 - Open Incident per User Location This query is the basis for a SRS report that accepts the AD Office and Department locations so that our various site managers can quickly pull a report of Active incidents for users at their site. This relies upon these attributes being accurate for the User objects in SCSM. In addition, we use the Cireson Web Console and allow these managers to access it. So the report gives them a quick summary and if they want details they just click the URL link and can see the 'live' incident. Big plug for Cireson - great app! SELECT inc.IncidentDimKey, wi.ID AS 'Incident ID', usr2.DisplayName AS 'Assigned To User' ,wi.Title ,'http://srvhojisv312:85/ViewIncident.aspx?id=' + wi.Id AS 'Link to Incident' ,usr.DisplayName AS 'Affected User' ,usr.Department ,inc.CreatedDate ,inc.Status FROM [DWDataMart].[dbo].[WorkItemDimvw] wi JOIN [DWDataMart].[dbo].[IncidentDimvw] inc on wi.EntityDimKey = inc.EntityDimKey JOIN dwdatamart.dbo.IncidentTierQueuesvw tqvw on inc.TierQueue_IncidentTierQueuesId = tqvw.IncidentTierQueuesId JOIN [DWDataMart].[dbo].[DisplayStringDim] disp on tqvw.EnumTypeId = disp.MPElementId JOIn [DWDataMart].[dbo].[WorkItemAffectedUserFactvw] aff on inc.IncidentDimKey = aff.WorkItemDimKey JOIN [DWDataMart].[dbo].[UserDimvw] usr on aff.WorkItemAffectedUser_UserDimKey = usr.UserDimKey JOIN DWDataMart.dbo.[WorkItemAssignedToUserFactvw] assusr on inc.IncidentDimKey = assusr.WorkItemDimKey JOIN DWDataMart.dbo.UserDimvw usr2 on assusr.WorkItemAssignedToUser_UserDimKey = usr2.UserDimKey where --wi.ID = 'IR2488' Disp.LanguageCode = 'ENU' --AND usr.Department IN (@Dept) AND usr.Office in (@Office) AND inc.Status LIKE '%Active%' ORDER BY inc.CreatedDate DESC
