Service Manager Reports: Incident Dataset Sample

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