Service Manager Reports: Incident Dataset Sample
The T-SQL query below returns a basic set of properties to generate a report of Incidents. It was written in a basic format to demostrate how to pull the required incident information, from the multiple DWDataMart Views.As the WorkItemDimvw is used as the base of the SELECT statement, the values selected could be easily modified to return information from Problems, Incidents, Activies, and Change Requests.
/*
File Name: SMIncidents.rsd
Created By: England, Matthew D.
Modified: 20151122:0440
Description: Returns Incident properties and related property values. This query is intended to be used by SSRS as a Shared Cached Dataset.
*/
SELECT
/* Entity Properties */
EntityDimvw.LastModified AS [Modified],
/* WorkItem Properties */
WorkItemDimvw.Id AS [Id],
WorkItemDimvw.IsDeleted AS [Archived],
/* Incident Properties */
IncidentDimvw.Title AS [Title],
IncidentDimvw.CreatedDate AS [Created],
IncidentDimvw.ResolvedDate AS [Resolved],
IncidentDimvw.ClosedDate AS [Closed],
IncidentDimvw.FirstResponseDate AS [First Response],
IncidentDimvw.FirstAssignedDate AS [First Assigned],
IncidentDimvw.Priority AS [Priority],
IncidentDimvw.Description AS [Description],
IncidentDimvw.IsParent AS [Is Parent],
IncidentDimvw.Major_Incident AS [Major Incident],
/* Look Up Values */
[Classification] = IncidentClassificationvw.IncidentClassificationValue,
[Source] = IncidentSourcevw.IncidentSourceValue,
[Impact] = IncidentImpactvw.IncidentImpactValue,
[Urgency] = IncidentUrgencyvw.IncidentUrgencyValue,
[Support Group] = IncidentTierQueuesvw.IncidentTierQueuesValue,
[Status] = IncidentStatusvw.IncidentStatusValue,
[Resolution Category] = IncidentResolutionCategoryvw.IncidentResolutionCategoryValue,
/* User Fields */
[Assigned To] = AssignedToUser.DisplayName,
[Affected User] = AffectedUser.DisplayName,
[Affected User Company] = AffectedUser.Company,
[Created By] = CreatedByUser.DisplayName,
[Primary Owner] = PrimaryOwner.DisplayName,
[Resolved By] = ResolvedByUser.DisplayName
FROM WorkItemDimvw
INNER JOIN EntityDimvw
ON WorkItemDimvw.EntityDimKey = EntityDimvw.EntityDimKey
INNER JOIN IncidentDimvw
ON IncidentDimvw.EntityDimKey = WorkItemDimvw.EntityDimKey
/* Joins for Lookup Values */
LEFT OUTER JOIN IncidentClassificationvw
ON IncidentDimvw.Classification_IncidentClassificationId = IncidentClassificationvw.IncidentClassificationId
LEFT OUTER JOIN IncidentSourcevw
ON IncidentDimvw.Source_IncidentSourceId = IncidentSourcevw.IncidentSourceId
LEFT OUTER JOIN IncidentImpactvw
ON IncidentDimvw.Impact_IncidentImpactId = IncidentImpactvw.IncidentImpactId
LEFT OUTER JOIN IncidentUrgencyvw
ON IncidentDimvw.Urgency_IncidentUrgencyId = IncidentUrgencyvw.IncidentUrgencyId
LEFT OUTER JOIN IncidentTierQueuesvw
ON IncidentDimvw.TierQueue_IncidentTierQueuesId = IncidentTierQueuesvw.IncidentTierQueuesId
LEFT OUTER JOIN IncidentStatusvw
ON IncidentDimvw.Status_IncidentStatusId = IncidentStatusvw.IncidentStatusId
LEFT OUTER JOIN IncidentResolutionCategoryvw
ON IncidentDimvw.ResolutionCategory_IncidentResolutionCategoryId = IncidentResolutionCategoryvw.IncidentResolutionCategoryId
/* Joins for User Relationships */
Left Outer Join dbo.WorkItemAssignedToUserFactvw
ON WorkItemDimvw.WorkItemDimKey = WorkItemAssignedToUserFactvw.WorkItemDimKey
And WorkItemAssignedToUserFactvw.DeletedDate IS NULL
Left Outer Join dbo.UserDimvw AssignedToUser
ON WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey
Left Outer Join dbo.WorkItemAffectedUserFactvw
ON WorkItemAffectedUserFactvw.WorkItemDimKey = WorkItemDimvw.WorkItemDimKey
And WorkItemAffectedUserFactvw.DeletedDate IS NULL
Left Outer Join dbo.UserDimvw AffectedUser
ON WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
Left Outer Join dbo.WorkItemCreatedByUserFactvw
ON WorkItemCreatedByUserFactvw.WorkItemDimKey = WorkItemDimvw.WorkItemDimKey
And WorkItemCreatedByUserFactvw.DeletedDate IS NULL
Left Outer Join dbo.UserDimvw CreatedByUser
ON WorkItemCreatedByUserFactvw.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
Left Outer Join IncidentHasPrimaryOwnerFactvw
ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = IncidentDimvw.IncidentDimKey
AND IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL
Left Outer Join dbo.UserDimvw PrimaryOwner
ON IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey = PrimaryOwner.UserDimKey
Left Outer Join dbo.IncidentResolvedByUserFactvw
ON IncidentResolvedByUserFactvw.IncidentDimKey = IncidentDimvw.IncidentDimKey
AND IncidentResolvedByUserFactvw.DeletedDate IS NULL
Left Outer Join dbo.UserDimvw ResolvedByUser
ON IncidentResolvedByUserFactvw.TroubleTicketResolvedByUser_UserDimKey = ResolvedByUser.UserDimKey
/* Where Clause Requires Parameters Defined in Report, or in TSQL depending on execution method.*/
WHERE IncidentDimvw.CreatedDate BETWEEN @StartDate AND @EndDate
