Is it possible to suppress projects in a project report if a user's tasks are complete?

[DeborahBaker]

Greetings! I'm interested in a Project Report, where a project is suppressed if a User's tasks are longer have active in that project. Can I do that with a Project Report? Or, will it need to be a Task Report? 

Here's a screenshot of my current filters in the Project Report. TY! Deb

[Madalyn_Destafney]

Hi there, it depends what info you're looking to see in your report. What is the goal of your report, to show users what outstanding/incomplete tasks assigned to them? If so, I'd create a task report - you can group by project if you want or have a column for project, but if you're wanting the report to show a list of tasks, rather than a list of projects, you'll want a task report.

If you want to use $$USER.ID, you can still use that in a task report with 'assignment users name.' If your team uses both tasks and issues and you want to show what they have assignments in for either, you'll want to use an Assignment report (pulls tasks and issues assignments).

If you found this helpful, please mark correct to help others : )

[DeborahBaker]

Greetings Madalyn,

Thank you for your response. The goal is to eliminate projects in the list where the User's tasks are complete, but the project may still be open. In many instances, there are 2-6 cross-functional teams in a project template that happen after a User's tasks are complete. Therefore, we would like that project to not be on the report.

Based on that goal, I'm not sure if your suggested steps are what will do the trick. 

[Madalyn_Destafney]

Still trying to grasp this. So each user wants to see projects they have tasks assigned to them in, and are still open, even if their tasks in the project are all completed? Trying to understand the use case for them wanting to see this on a project level instead of what tasks are outstanding. Is this primarily for project owners of those incomplete projects?

[skyehansen]

hi Deborah, in any situation like this, where you are calling on a one-to-many relationship (one project has many tasks and issues), there's a better than average probability that you'll have to use an EXISTS statement to get you what you need. To do this, you would need to be comfortable with leaving standard mode and switching to text mode, as well as at least making minor corrections.

Let's take this textmode below as an example.

EXISTS:a:$$EXISTSMOD=EXISTS
EXISTS:a:$$OBJCODE=OPTASK
EXISTS:a:actualCompletionDate_Mod=isnull
EXISTS:a:assignmentsUsersMM:ID=$$USER.ID
EXISTS:a:assignmentsUsersMM:ID_Mod=in
EXISTS:a:projectID=FIELD:ID
statusEquatesWith=DED	CPL
statusEquatesWith_Mod=notin
OR:1:EXISTS:a:$$EXISTSMOD=EXISTS
OR:1:EXISTS:a:$$OBJCODE=TASK
OR:1:EXISTS:a:actualCompletionDate_Mod=isnull
OR:1:EXISTS:a:assignmentsUsersMM:ID=$$USER.ID
OR:1:EXISTS:a:assignmentsUsersMM:ID_Mod=in
OR:1:EXISTS:a:projectID=FIELD:ID
OR:1:statusEquatesWith=DED	CPL
OR:1:statusEquatesWith_Mod=notin

The first 8 lines say "if there's an issue assigned to the user, that isn't complete, AND the project 'status equates with' field is NOT Complete/Cancelled, please list the project in the report"

The next 8 lines say "if there's a task assigned to the user, that isn't complete, AND the project 'status equates with' field is NOT Complete/Cancelled, please list the project in the report"

So the combination is "if there's any work that isn't complete and is assigned to the viewer, and if the project is not closed/cancelled, list the project"

As an aside:  I really encourage you to use "status equates with" rather than "status" -- if you spend a lot of time creating custom statuses, this can cut down on the maintenance of having to locate your reports to add any new statuses you create. If you want to stick with "status", just change your filters above into text mode and copy the relevant parts to sub into the above.

For more on EXISTS, please read up here: https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/text-mode/create-complex-t...

[DeborahBaker]

Greetings!

Thank you for your response! I tried what you suggested text above, put into the Filter section, and now all of my Cancelled projects are showing up.

I stated this above for Madelyn and wanted to also share with you, as it may change the nature of your response:

The goal of this report is to eliminate projects in the list where the User's tasks are complete within any projects that are Current, but the project may still be open. In many instances, there are 2-6 cross-functional teams in a project template that happen after a User's tasks are complete. Therefore, we would like that project to not be on the report.

With that said, is your suggested approach correct?

[skyehansen]

good morning! Yes. It's a common need. We use this exact filter in one of our reports and I show 35 projects, none of which are cancelled.

Without being able to see your code, a couple of things could be happening.

1) sometimes copy/paste brings in tab characters incorrectly. For the two "statusEquatesWith=DED" lines above, the character immediately after "DED" is a tab character

2) is your cancelled status equated to Dead? Go to https://<yourdomain>.my.workfront.com/setup/statuses to check

Feel free to copy your code back in here as well so we can all troubleshoot.

Last resort -- use the status line instead, since that one appears to work (it will just always need that additional maintenance). To do this, start over with your filters (delete everything), put your "status equals" filter back in as you had it above, switch it over to text mode, and in my block of code, just substitute it in for the 4 "status equates with" lines. (so copy the two lines to make 4, and add "OR:1:" to the copy. Again... if you want to paste your code back in here, it will be easier to troubleshoot if something has gone awry.

[Doug_Den_Hoed__AtAppStore]

Excellent tips, @skyehansen  -- I concur.

Regards,

Doug

[DeborahBaker]

Hello Again, Your edits solved the issues!

I have one more question, then I would consider this a complete thread - how should I handle On Hold projects? Those are also showing up, and we don't want them to populate the report. 

Thank you so much for your help! It's really cool to see it all come together and understand the logic for the text editing tool. If I don't hear from you beforehand, have a lovely weekend! 

[skyehansen · Accepted solution]

Others might know better than me. At a guess, I would try and add one more acronym to the 2 statusEquatesWith lines. So it would read DED, tab, CPL, tab, ONH. I'm not entirely sure this would work since I haven't tested it, but that's my guess! Give it a shot.

[DeborahBaker]

Perfection! Thank you SO much!