Query unassigned tasks

Message from <Dave.Beliveau@sanmina-sci.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hello,

We use MS SQL Server 2000.

This auto livereport summarizes task list information by listing individual tasks, their parent, status, Instructions and Comments broken out into individual columns, Due Date, and Assignee.

The problem I have is that the report only returns tasks that are assigned and I would like it to return all tasks whether assigned or not.

Any help would be appreciated.

select (CASE a.status When 0 Then 'Pending' When 1 Then 'In Process' When 2 Then 'Issue' When 3 Then 'On Hold' When -1 Then 'Completed' When -2 Then 'Cancelled' Else 'Unknown' End) as Status, b.name as Parent, a.name as Task, SUBSTRING(a.extendedData,CHARINDEX('Instructions',a.extendedData)+15,CHARINDEX('>',a.extendedData) - CHARINDEX('Instructions',a.extendedData)- 16) AS Instructions,SUBSTRING(a.extendedData,19, CHARINDEX('Instructions',a.extendedData)-22) as Comments, KUAF.Name as Owner, a.DateDue as Due from DTree a, Dtree b, KUAF where a.parentid=b.dataid and a.SubType=206 and KUAF.ID=a.Assignedto and((a.Status=0) or (a.Status=1) or (a.Status=2) or (a.Status=3) or (a.Status=-1) or (a.Status=-2)) and (a.OwnerID IN (<task list objectID>)) order by a.ownerID DESC, a.status

Thanks,

Dave Beliveau

Find more posts tagged with

Comments

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard via eLinkTry taking out the assignment condition - KUAF.ID=a.Assignedto-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tue 4/5/2005 12:02 PMTo: eLink RecipientSubject: Query unassigned tasks Query unassigned tasks Posted by SANMIN013 (Beliveau, David) on 04/05/2005 12:02 PM Message from via eLinkHello, We use MS SQL Server 2000. This auto livereport summarizes task list information by listing individual tasks, their parent, status, Instructions and Comments broken out into individual columns, Due Date, and Assignee. The problem I have is that the report only returns tasks that are assigned and I would like it to return all tasks whether assigned or not. Any help would be appreciated. select (CASE a.status When 0 Then 'Pending' When 1 Then 'In Process' When 2 Then 'Issue' When 3 Then 'On Hold' When -1 Then 'Completed' When -2 Then 'Cancelled' Else 'Unknown' End) as Status, b.name as Parent, a.name as Task, SUBSTRING(a.extendedData,CHARINDEX('Instructions',a.extendedData)+15,CHARINDEX('>',a.extendedData) - CHARINDEX('Instructions',a.extendedData)- 16) AS Instructions,SUBSTRING(a.extendedData,19, CHARINDEX('Instructions',a.extendedData)-22) as Comments, KUAF.Name as Owner, a.DateDue as Due from DTree a, Dtree b, KUAF where a.parentid=b.dataid and a.SubType=206 and KUAF.ID=a.Assignedto and((a.Status=0) or (a.Status=1) or (a.Status=2) or (a.Status=3) or (a.Status=-1) or (a.Status=-2)) and (a.OwnerID IN ()) order by a.ownerID DESC, a.status Thanks,Dave Beliveau ________________________________[To reply to this thread, use your normal E-mail reply function.] Discussion: Livelink LiveReports Discussion Livelink Server: Knowledge Center

David_Beliveau_(sanmin013_-_(deleted))

Message from <Dave.Beliveau@sanmina-sci.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">RE: RE Query unassigned tasks

When I do that I get one task repeated up to the record limit for the report and Assigned to column field displays random names or groups.

Dave

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, April 05, 2005 2:01 PM
To: eLink Recipient
Subject: RE Query unassigned tasks

RE Query unassigned tasks
Posted by Olson, Len on 04/05/2005 02:00 PM

Message from Olson, Leonard <len.olson@rgsinc.com> via eLink

Try taking out the assignment condition - KUAF.ID=a.Assignedto

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tue 4/5/2005 12:02 PM
To: eLink Recipient
Subject: Query unassigned tasks

Query unassigned tasks <https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3906219&objAction=view> Posted by SANMIN013 <mailto:dave.beliveau@sanmina-sci.com> (Beliveau, David) on 04/05/2005 12:02 PM

Message from <Dave.Beliveau@sanmina-sci.com> via eLink

Hello,

We use MS SQL Server 2000.

This auto livereport summarizes task list information by listing individual tasks, their parent, status, Instructions and Comments broken out into individual columns, Due Date, and Assignee.

The problem I have is that the report only returns tasks that are assigned and I would like it to return all tasks whether assigned or not.

Any help would be appreciated.

select (CASE a.status When 0 Then 'Pending' When 1 Then 'In Process' When 2 Then 'Issue' When 3 Then 'On Hold' When -1 Then 'Completed' When -2 Then 'Cancelled' Else 'Unknown' End) as Status, b.name as Parent, a.name as Task, SUBSTRING(a.extendedData,CHARINDEX('Instructions',a.extendedData)+15,CHARINDEX('>',a.extendedData) - CHARINDEX('Instructions',a.extendedData)- 16) AS Instructions,SUBSTRING(a.extendedData,19, CHARINDEX('Instructions',a.extendedData)-22) as Comments, KUAF.Name as Owner, a.DateDue as Due from DTree a, Dtree b, KUAF where a.parentid=b.dataid and a.SubType=206 and KUAF.ID=a.Assignedto and((a.Status=0) or (a.Status=1) or (a.Status=2) or (a.Status=3) or (a.Status=-1) or (a.Status=-2)) and (a.OwnerID IN (<task list objectID>)) order by a.ownerID DESC, a.status

Thanks,
Dave Beliveau

________________________________

[To reply to this thread, use your normal E-mail reply function.]
Discussion: Livelink LiveReports Discussion <https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view>

Livelink Server: Knowledge Center <https://knowledge.opentext.com/knowledge/livelink.exe>

[To reply to this thread, use your normal E-mail reply function.]

============================================================

Topic: Query unassigned tasks
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3906219&objAction=view

Discussion: Livelink LiveReports Discussion https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view

Livelink Server:
https://knowledge.opentext.com/knowledge/livelink.exe

_____________________________________________________________________________
Scanned by Sanmina-SCI eShield _____________________________________________________________________________

Dan_Friend

By now you probably already have this figured out, but you probably need to make the a.assigneto an outer join. That way it will pull all the tasks even if it's not assigned.Dan

David_Beliveau_(sanmin013_-_(deleted))

Message from <Dave.Beliveau@sanmina-sci.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">RE: By now you probably already have this figured out, but you probably need to...

I did a full outer join on Dtree and Kuaf and now it works! Thanks for the help.

For anyone interested, I have attached the report, written for MS SQL. Please note, the task list objectID's are coded into the SQL here and would need to be changed to reflect your own id's.

Dave

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, April 20, 2005 4:01 PM
To: eLink Recipient
Subject: By now you probably already have this figured out, but you probably need to...

By now you probably already have this figured out, but you probably need to...
Posted by Friend, Dan on 04/20/2005 03:58 PM

By now you probably already have this figured out, but you probably need to make the a.assigneto an outer join. That way it will pull all the tasks even if it's not assigned.

Dan

[To reply to this thread, use your normal E-mail reply function.]

============================================================

Topic: Query unassigned tasks
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3906219&objAction=view

Discussion: Livelink LiveReports Discussion https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view

Livelink Server:
https://knowledge.opentext.com/knowledge/livelink.exe

_____________________________________________________________________________
Scanned by Sanmina-SCI eShield _____________________________________________________________________________