Project Report for only tasks that are open for assignee

[JanelleHi]

We currently use a project report that users can see a list of open project on which they're a team member. A few users have asked if the report can be revised to only show projects on which they still have open tasks. They rather not see projects in their list if all if their tasks are completed, even if the project is still open for others to work on.

I prefer not to use a task report in this instance- just need project info, not a list of tasks.

I know there are (severe) limitations on crossing project and task information in reports, but wondering if anyone has been able to solve for something like this?

Thanks!

[AnthonyIm · Accepted solution]

Hi - this was a fun challenge to end my week! I think I got it figured out for you using an EXIST filter. Hope it helps you.

EXISTS:1:$$OBJCODE=ASSGN

EXISTS:1:projectID=FIELD:ID

EXISTS:1:assignedToID=$$USER.ID

EXISTS:1:assignedToID_Mod=in

EXISTS:1:task:status=CPL

EXISTS:1:task:status_Mod=notin

EXIST filters let you jump an object type (so in this case I wanted to do Assignment filters on a Project report (skipping the task hierarchy). It is also great when you are moving down the hierarchy instead of up.

Here is my little cheat sheet when you want to move down the hierarchy. Hopefully it makes sense. It relies on the API Explorer (https://one.workfront.com/s/api-explorer)

Moving Down The Hierarchy

This is for reports where you want to apply a filter based on a many-to-one relationship (Like portfolios that have projects Anthony owns)

EXISTS:1:$$OBJCODE=[Insert Object Code for the Collection]

EXISTS:1:[Collection Object Type's field that matches current object]=FIELD:[Current Object Type's field that matches collection object]

EXISTS:1:[Field on Collection being filtered]=[Condition]

• Tip: Start to create a report on the collection's object type level (in this case a Project report) applying the filters you want, switch to text mode, and then copy the filter. Then paste it in your actual report (in this case a Portfolio report) and add EXISTS:1: in front of each line.

Based on my Example:

EXISTS:1:$$OBJCODE=PROJ

EXISTS:1:portfolioID=FIELD:ID

EXISTS:1:ownerID=532376910024d8443277c3e46ed5330a

EXISTS:1:ownerID_Mod=in

[JanelleHi]

Thank you so much, this was really helpful. I also added an EXISTS:1 for 'project status equates with' to just show those that are 'open'. So far it seems to be what I needed. Thank you!