@N INNER JOIN BasicItemRevision_View CRev ON CRev.ItemId=CI.BasicItem AND CRev.FromNum <= @N AND CRev.ToNum > @N WHERE CI.FromNum <= @N AND CI.ToNum > @N AND CI.ParentItem=@ItmId UNION ALL SELECT Chld.[Id] ,Chld.[ParentItem] ,PRev.[Id] AS [ParentRevId] ,CITree.[Level] + 1 ,Chld.[BasicItem] ,CRev.[Id] ,Chld.[Notes] FROM dbo.ChildItem Chld INNER JOIN CITree ON Chld.ParentItem = CITree.ItemId INNER JOIN BasicItemRevision_View PRev ON PRev.ItemId=Chld.ParentItem AND PRev.FromNum <= @N AND PRev.ToNum > @N INNER JOIN BasicItemRevision_View CRev ON CRev.ItemId=Chld.BasicItem AND CRev.FromNum <= @N AND CRev.ToNum > @N WHERE Chld.[FromNum] <= @N AND Chld.[ToNum] > @N AND CITree.[Level] < 20 ) , ParentTree AS ( SELECT @emptyId AS [RowId] ,@emptyId AS [ParentItem] ,@emptyId AS [ParentRevId] ,0 AS [Level] ,[ItemId] AS [ItemId] ,[Id] AS [RevId] ,'' AS [Notes] FROM dbo.[BasicItemRevision_View] WHERE Id=@P1 UNION ALL SELECT * FROM CITree ) , TaskList AS ( SELECT [ItemRevision] ,[Subject] ,[AssignedTo] ,[DueBy] ,[Priority] ,[Status] FROM [PDXpertDB].[dbo].[UserTask_View] ) , RevTasks AS ( SELECT DISTINCT IV.OwnerShort AS [Owner] ,IV.Number + ' (' + IV.TypeShort + ')' AS [Number] ,IV.Description AS [Description] ,PT.[RevId] FROM ParentTree PT INNER JOIN viewer.[ItemView] IV ON IV.[ItemId] = PT.ItemId WHERE IV.[PendingRevID] IS NOT NULL ) SELECT RT.Owner + ' ' + RT.Number + ' ' + CASE WHEN Len(Replace(Replace(RT.Description, char(13), ''), char(10), '')) > 100 THEN RTrim(Left(Replace(Replace(RT.Description, char(13), ''), char(10), ''), 97)) + '...' ELSE Replace(Replace(RT.Description, char(13), ''), char(10), '') END AS [Item] ,CASE WHEN Tsk.Priority=0 THEN 'High' WHEN Tsk.Priority=1 THEN 'Medium' WHEN Tsk.Priority=2 THEN 'Low' ELSE '?' END AS [Priority] ,Left(Convert(nvarchar(10), Tsk.DueBy, 120), 10) AS [DueOn] ,CASE WHEN Tsk.Status=0 THEN 'Not started' WHEN Tsk.Status=1 THEN 'In progress' WHEN Tsk.Status=2 THEN 'Waiting on another' WHEN Tsk.Status=3 THEN 'Completed' WHEN Tsk.Status=4 THEN 'Deferred' WHEN Tsk.Status=5 THEN 'Canceled' ELSE '?' END AS [Status] ,PTo.Name AS [AssignedTo] ,Tsk.Subject AS [Task] FROM RevTasks AS RT INNER JOIN TaskList Tsk ON Tsk.ItemRevision=RT.RevId INNER JOIN Person_View PTo ON PTo.Id=Tsk.AssignedTo WHERE Tsk.Status <> 3 ORDER BY RT.Owner, RT.Number, DueOn DESC, Tsk.Priority ]]> 1.5in 1in 1in 1.5in 1.5in 3.25in 0.25in true true Item DimGray Middle 2pt 2pt 2pt 2pt true true Priority DimGray Middle 2pt 2pt 2pt 2pt true true Due On DimGray Middle 2pt 2pt 2pt 2pt true true Status DimGray Middle 2pt 2pt 2pt 2pt true true Assigned To DimGray Middle 2pt 2pt 2pt 2pt true true Task DimGray Middle 2pt 2pt 2pt 2pt 0.25in true true =Fields!Item.Value 2pt 2pt 2pt 2pt 6 0.25in true true 2pt 2pt 2pt 2pt true true =Fields!Priority.Value 2pt 2pt 2pt 2pt true true =Fields!DueOn.Value 2pt 2pt 2pt 2pt true true =Fields!Status.Value 2pt 2pt 2pt 2pt true true =Fields!AssignedTo.Value 2pt 2pt 2pt 2pt true true =Fields!Task.Value 2pt 2pt 2pt 2pt =Fields!Item.Value =Fields!Item.Value After true DataSet1 0.0625in 0.75in 9.75in 0.76042in 10in 0.655in true true 0.61333in 0.0625in 0in 9.875in 2pt true true PDXpert Product Lifecycle Manager 0.06125in 5.9375in 0.25in 4in 1 2pt 2pt 2pt 2pt true true Item Incomplete Tasks 2pt 2pt 2pt 2pt true true =Globals!ExecutionTime 0.33903in 5.9375in 0.19097in 4in 3 Middle 2pt 2pt 2pt 2pt 0.39583in true true 0.05in 0.0625in 0in 9.875in 2pt true true Page =Globals!PageNumber of =Globals!TotalPages 0.10556in 5.9375in 0.23611in 4in 1 Middle 2pt 2pt 2pt 2pt 1in 1in 1in 1in