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