Report to show programs where all projects are completed

[NickVa1]

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

[Richard_Le_ · Accepted solution]

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. 

[NickVa1]

Wonderful, this worked!  Thanks, Rich!

[Richard_Le_]

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? 

[Doug_Den_Hoed__AtAppStore]

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

Regards,
Doug

[NickVa1]

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