Review Activity in Progress (CR Reporting)

Out of the box in Service Manager (console) there is a view Change Requests: In review. Although if you open the criteria for the view it does not really show it, it displays all CRs with a RA In Progress.

We created a report from Service Manager DB which works to an extent to replicate this view but is frequently missing CRs, seems to be if they are recently edited.

The view is SM Console is reliable so our Change Managers are using this to check they have them all as a work around but cannot seen to get a reliable list form the portal. Open to suggestions :)

This is our SQL:

SELECT wi.WorkItemId AS [CR_Id], cr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'Title', catds.DisplayName as Category,

PriorityDS.DisplayName as 'Priority', SuppGrpDS.DisplayName as 'SupportGroup', wi.AssignedUser, wi.AffectedUser, 

CR.ScheduledStartDate_89429D01_365C_366D_FCDA_3198102B180C as ScheduledStartDate,

CR.ScheduledEndDate_6FC72C26_565D_CB2A_BBAF_6A699C15FE99 as ScheduledEndDate

FROM Servicemanager.dbo.MTV_System$WorkItem$ChangeRequest [CR]

LEFT JOIN Servicemanager.dbo.DisplayStringView as [catds] ON catds.LTStringId = cr.Category_7B1892FE_108A_EC85_064D_7815C2DFC442 AND catds.LanguageCode = 'enu'

LEFT JOIN ServiceManager.dbo.DisplayStringView [StatusDS] ON StatusDS.LTStringId = cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138 AND StatusDS.LanguageCode = 'ENU'

LEFT JOIN ServiceManager.dbo.DisplayStringView [SuppGrpDS] ON CR.SupportGroup_4A79F4D2_652F_0653_5604_98E1B732ECDD = SuppGrpDS.LTStringId AND SuppGrpDS.LanguageCode = 'ENU'

LEFT JOIN ServiceManagement.dbo.WorkItem [wi] ON cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C = WI.WorkItemId

LEFT JOIN ServiceManager.dbo.DisplayStringView [PriorityDS] ON wi.PriorityId = PriorityDS.LTStringID AND PriorityDS.LanguageCode = 'ENU'

WHERE StatusDS.DisplayName NOT IN ('Closed','Cancelled','Failed','Completed')

AND CR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C Like 'CR%' and CR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IN

(Select ParentWorkItemId

From ServiceManagement.dbo.Workitem as WI

LEFT OUTER JOIN ServiceManagement.dbo.DisplayString as DSStatus2 on StatusId = DSStatus2.ElementID

Where WI.WorkItemId LIKE 'RA%' and DSStatus2.DisplayString = 'In Progress')