SCSM Reporting: Dataset for Incident Requests

It’s Christmas time again, and that means it’s time for another SQL query for Service Manager reporting. :)

This query gives you a dataset in list form to work with in Excel or PowerBI, containing the following informations related to Incident Requests.

ID
Classification
Closed Date
Created Date
Created Day
Created Month
Created Year
Created Week
First Response Date
Priority
Resolution Category
Resolved Date
Resolved Day
Resolved Month
Resolved Year
Resolved Week
Source
Status
TargetResolutionTime
TierQueue

You will need to run the SQL query against the Service Manager Data Warehouse database DWDataMart.

/****** Developed by Brian Fahrenholtz (Coretech A/S)  ******/
SELECT
'1' AS IncidentDimCount,
I.Id,
Classification = ISNULL(ClassificationEnumDS.DisplayName, ClassificationEnum.IncidentClassificationValue),
Convert(Date,I.ClosedDate) As 'Closed Date',
I.CreatedDate,
DATEPART(DAY,I.CreatedDate) AS 'Created Day',
DATEPART(MONTH,I.CreatedDate) AS 'Created Month',
DATEPART(YEAR,I.CreatedDate) AS 'Created Year',
DATEPART(WEEK,I.CreatedDate) AS 'Created Week',
I.FirstResponseDate AS 'First Response Date',
I.Priority,
ResolutionCategory = ISNULL(ResolutionCategoryDS.DisplayName, ResolutionCategoryEnum.IncidentResolutionCategoryValue),
I.ResolvedDate AS 'Resolved Date',
DATEPART(DAY,I.ResolvedDate) AS 'Resolved Day',
DATEPART(MONTH,I.ResolvedDate) AS 'Resolved Month',
DATEPART(YEAR,I.ResolvedDate) AS 'Resolved Year',
DATEPART(WEEK,I.ResolvedDate) AS 'Resolved Week',
Source = ISNULL(SourceDS.DisplayName, SourceEnum.IncidentSourceValue),
Status = ISNULL(StatusDS.DisplayName, StatusEnum.IncidentStatusValue),
I.TargetResolutionTime,
TierQueue = ISNULL(TierQueueEnumDS.DisplayName, TierQueueEnum.IncidentTierQueuesValue)

FROM
DWDataMart.dbo.IncidentDimvw I

	INNER JOIN DWDAtaMart.dbo.EntityDimvw Entity
	ON I.EntityDimKey = Entity.EntityDimKey

	INNER JOIN DWDAtaMart.dbo.WorkItemDimvw WI
	ON I.EntityDimKey = WI.EntityDimKey

	/****** Tier Queue ******/
	Left Outer Join DWDataMart.dbo.IncidentTierQueuesvw TierQueueEnum
	ON  I.TierQueue_IncidentTierQueuesId = TierQueueEnum.IncidentTierQueuesId
		
	Left Outer Join DWDataMart.dbo.DisplayStringDimvw TierQueueEnumDS
	ON TierQueueEnum.EnumTypeId = TierQueueEnumDS.BaseManagedEntityId
	AND TierQueueEnumDS.LanguageCode = 'ENU'	
	
	/****** Source ******/
	LEFT OUTER JOIN
	DWDataMart.dbo.IncidentSourcevw AS SourceEnum 
	ON SourceEnum.IncidentSourceId = I.Source_IncidentSourceId  
	
	LEFT OUTER JOIN 
	DWDataMart.dbo.DisplayStringDimvw SourceDS
	ON SourceEnum.EnumTypeId=SourceDS.BaseManagedEntityId
	AND SourceDS.LanguageCode = 'ENU'

	/****** Resolution Category ******/
	LEFT OUTER JOIN
	DWDataMart.dbo.IncidentResolutionCategoryvw AS ResolutionCategoryEnum 
	ON ResolutionCategoryEnum.IncidentResolutionCategoryId = I.ResolutionCategory_IncidentResolutionCategoryId  
	
	LEFT OUTER JOIN 
	DWDataMart.dbo.DisplayStringDimvw AS ResolutionCategoryDS
	ON ResolutionCategoryEnum.EnumTypeId=ResolutionCategoryDS.BaseManagedEntityId
	AND ResolutionCategoryDS.LanguageCode = 'ENU'

	/****** Classification ******/
	Left Outer Join DWDataMart.dbo.IncidentClassificationvw ClassificationEnum 
	ON I.Classification_IncidentClassificationId = ClassificationEnum.IncidentClassificationId
	
	Left Outer Join DWDataMart.dbo.DisplayStringDimvw ClassificationEnumDS
	ON ClassificationEnum.EnumTypeId = ClassificationEnumDS.BaseManagedEntityId
	AND ClassificationEnumDS.LanguageCode = 'ENU'

	/****** Status ******/
	LEFT OUTER JOIN
	DWDataMart.dbo.IncidentStatusvw AS StatusEnum 
	ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId  
	
	LEFT OUTER JOIN 
	DWDataMart.dbo.DisplayStringDimvw StatusDS
	ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
	AND StatusDS.LanguageCode = 'ENU'

	
Order by I.CreatedDate

The output will be like below, when you using it in Excel.

Untitled

Mary Christmas


Leave a Reply