Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!
SOLVED

Report to show programs where all projects are completed

Avatar

Level 10

Hi WF Community,

 

I'm trying to figure out how to create a report - that will be part of a dashboard added as a custom tab at the portfolio level - whereby we can surface only programs where all of the projects with them have either project Status of "Complete" or "Scoping Archived".

I would I need to leverage EXISTS filtering in my report?  Anyone able to provide some guideance or an example?

Thanks,
Nick

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hey Nick,

 

I've been having a play around with this and I believe that the below EXISTS statement should do it:

 

EXISTS:a:$$EXISTSMOD=EXISTS
EXISTS:a:$$OBJCODE=PROJ
EXISTS:a:programID=FIELD:ID
EXISTS:a:status=CPL
EXISTS:a:status_Mod=in
AND:1:EXISTS:a:$$EXISTSMOD=NOTEXISTS
AND:1:EXISTS:a:$$OBJCODE=PROJ
AND:1:EXISTS:a:programID=FIELD:ID
AND:1:EXISTS:a:status=DED	PLN	CUR
AND:1:EXISTS:a:status_Mod=in

This code is effectively telling Workfront, show me all programs that contain projects in a complete status AND where there are no projects in a Dead, Planning or Current state. 

 

You'd obviously need to tweak this to your own requirements adding the status code for "Scoping Archived" to the EXISTS statement, and any other statuses you need to exluded to the NOTEXISTS statement . . .but hopefully that gets you somewhere close to what you need. 

 

Let me know how you get on.

 

Regards,

Rich. 

View solution in original post

5 Replies

Avatar

Correct answer by
Community Advisor

Hey Nick,

 

I've been having a play around with this and I believe that the below EXISTS statement should do it:

 

EXISTS:a:$$EXISTSMOD=EXISTS
EXISTS:a:$$OBJCODE=PROJ
EXISTS:a:programID=FIELD:ID
EXISTS:a:status=CPL
EXISTS:a:status_Mod=in
AND:1:EXISTS:a:$$EXISTSMOD=NOTEXISTS
AND:1:EXISTS:a:$$OBJCODE=PROJ
AND:1:EXISTS:a:programID=FIELD:ID
AND:1:EXISTS:a:status=DED	PLN	CUR
AND:1:EXISTS:a:status_Mod=in

This code is effectively telling Workfront, show me all programs that contain projects in a complete status AND where there are no projects in a Dead, Planning or Current state. 

 

You'd obviously need to tweak this to your own requirements adding the status code for "Scoping Archived" to the EXISTS statement, and any other statuses you need to exluded to the NOTEXISTS statement . . .but hopefully that gets you somewhere close to what you need. 

 

Let me know how you get on.

 

Regards,

Rich. 

Avatar

Community Advisor

You're welcome Nick. If this solved your problem, please can you mark it as an accepted solution so that others know that this code works in the future? 

Avatar

Community Advisor

 

Very nice, Rich: it's in The Vault!

 

Regards,
Doug

Avatar

Level 10

Ahh, so that's how you mark something as the best answer here in Experience League.  Done!