I need a report to list all Active Programs with no Active Projects in them. | Community
Skip to main content
L
LCarper
Level 2
December 12, 2023
Solved

I need a report to list all Active Programs with no Active Projects in them.

  • December 12, 2023
  • 4 replies
  • 1140 views

I'm trying to create a report that will give me a list of all Active Programs that have only Completed Projects within them.  

 

Best answer by skyehansen

hi, this can be a program report. You would need to create an "exists statement" filter, or more to the point, a "not-exists statement," so your first step would be to utilize this knowledge:

https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/text-mode/create-complex-text-mode-filters-using-exists-statements.html?lang=en 

(Please note: this article clearly explains how to look for things that are "missing" and this is what you are actually asking for. i.e. In order to filter for "only completed projects" you are actually filtering for programs where the incomplete projects are missing.)

 

To create the program report:

 

Step 1: How would you normally locate completed projects? Or non-complete projects?

For non complete projects, I would use a project report, and filter on "status equates with" = current, planning or on hold. (everything else is complete or cancelled)

Switch to text mode and copy this line -- you'll use it in your program report:

statusEquatesWith=CUR PLN ONH

 

Step 2: Create your program report and put in all your normal program filters -- in this case, you are only looking for active programs, so filter on Program / is Active / true

 

Step 3: Your exists statement "connector" starts out with:

EXISTS:a:$$OBJCODE=PROJ EXISTS:a:programID=FIELD:ID

I call this the connector because it will connect your program report with all the project information. Line 1 says "there exists a project..." and line 2 says "... where the project's Program ID field, is the same as the ID field in this program report"

 

Step 4: Your exists statement will end with your project filter, and the "not exists" modifier.

EXISTS:a:$$EXISTSMOD=NOTEXISTS EXISTS:a:statusEquatesWith=CUR PLN ONH

 Please note the tab separators between each status.

 

Altogether in your program report filter:

isActive=true isActive_Mod=eq EXISTS:a:$$OBJCODE=PROJ EXISTS:a:programID=FIELD:ID EXISTS:a:$$EXISTSMOD=NOTEXISTS EXISTS:a:statusEquatesWith=CUR PLN ONH

Line 1 and 2: I am looking for all programs that are active, PLUS

Line 3 and 4: in every program with an ID, there will exist a project with the same program ID, WHERE

Line 5 and 6: the project status is NOT current, planning, or on hold (i.e. the project status IS complete or cancelled)

4 replies

LeslieSpier
Adobe Employee
LeslieSpierAdobe Employee
Adobe Employee
December 13, 2023

Maybe someone will have a better idea-- this doesn't require any text mode but does have a manual aspect to it. Hoping it at least gives you a starting place!

 

Build a project report and add the following filters and groupings, then manually look for programs with ONLY the complete status underneath it:

 

 

KellieGardner
Community Advisor
KellieGardnerCommunity Advisor
Community Advisor
December 13, 2023

Similar to Leslie's approach I would reverse engineer the report and start with a project report, add filters for the program/project criteria, then group by program name. On the summary tab it would list your programs that match the filter criteria.

 

 

skyehansen
Community Advisor
skyehansenCommunity AdvisorAccepted solution
December 13, 2023

hi, this can be a program report. You would need to create an "exists statement" filter, or more to the point, a "not-exists statement," so your first step would be to utilize this knowledge:

https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/text-mode/create-complex-text-mode-filters-using-exists-statements.html?lang=en 

(Please note: this article clearly explains how to look for things that are "missing" and this is what you are actually asking for. i.e. In order to filter for "only completed projects" you are actually filtering for programs where the incomplete projects are missing.)

 

To create the program report:

 

Step 1: How would you normally locate completed projects? Or non-complete projects?

For non complete projects, I would use a project report, and filter on "status equates with" = current, planning or on hold. (everything else is complete or cancelled)

Switch to text mode and copy this line -- you'll use it in your program report:

statusEquatesWith=CUR PLN ONH

 

Step 2: Create your program report and put in all your normal program filters -- in this case, you are only looking for active programs, so filter on Program / is Active / true

 

Step 3: Your exists statement "connector" starts out with:

EXISTS:a:$$OBJCODE=PROJ EXISTS:a:programID=FIELD:ID

I call this the connector because it will connect your program report with all the project information. Line 1 says "there exists a project..." and line 2 says "... where the project's Program ID field, is the same as the ID field in this program report"

 

Step 4: Your exists statement will end with your project filter, and the "not exists" modifier.

EXISTS:a:$$EXISTSMOD=NOTEXISTS EXISTS:a:statusEquatesWith=CUR PLN ONH

 Please note the tab separators between each status.

 

Altogether in your program report filter:

isActive=true isActive_Mod=eq EXISTS:a:$$OBJCODE=PROJ EXISTS:a:programID=FIELD:ID EXISTS:a:$$EXISTSMOD=NOTEXISTS EXISTS:a:statusEquatesWith=CUR PLN ONH

Line 1 and 2: I am looking for all programs that are active, PLUS

Line 3 and 4: in every program with an ID, there will exist a project with the same program ID, WHERE

Line 5 and 6: the project status is NOT current, planning, or on hold (i.e. the project status IS complete or cancelled)

    Madalyn_Destafney
    Community Advisor
    Madalyn_DestafneyCommunity Advisor
    Community Advisor
    August 5, 2025

    Hi Skye! I'm trying to do something similar and found this thread. I'm trying to get a list of active programs in 1 portfolio that have NO projects. In a program report, know I'd need some kind of text mode for true/equals, but what about NOT Exists instead of the usual EXISTS? is it like the below?

    isActive=true isActive_Mod=eq NOTEXISTS:a:$$OBJCODE=PROJ

     

    If this helped you, please mark correct to help others : )
    skyehansen
    Community Advisor
    skyehansenCommunity Advisor
    August 5, 2025

    no, if it's a program report it would be more like

     

    1. isActive=true
    2. isActive_Mod=eq
    3. EXISTS:a:$$OBJCODE=PROJ
    4. EXISTS:a:programID=FIELD:ID
    5. EXISTS:a:$$EXISTSMOD=NOTEXISTS

     

    Lines 1 and 2 -- program is active

    Line 3 -- always required -- what are you looking for (projects)

    Line 4 -- always required -- a linking line (for any project you find, the project program ID = your program's ID in this report)

    Line 5 -- your not exists line -- meaning, only list cases where it doesn't exist

    L
    LCarperAuthor
    Level 2
    December 13, 2023

    Thank you all.  I think I can do what I need now!

      Terms & ConditionsAccessibility statement

      Loading footer...