@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
0.06125in
0.0625in
0.25in
5.25in
2
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
0
System.Data.DataSet
/* Local Connection */
DataSet1
/* Local Query */
Item
Priority
DueOn
Status
AssignedTo
Task
Report2