Report filter based on task name & status combination

[shirinshaikh]

Hello,

I need to create a report to filter out 2 specific tasks:

1) Task 1 - Asset Audit

2) Task 2 - Build

I want to filter out projects where Task 2 is in progress and Task 1 is not complete.

Task 1 - Actual end date is blank & Task 2 - Actual start end is not blank

When I tried "OR" in the filter, it either shows Task 1 / Task 2. I need to filter out the combination of task 1 + status & task 2 + status. 

Is there a way to create a filter for this combination?

[Sven-iX]

Why are you using an OR filter? Your definition above sounds like you want to AND the 2 conditions? 

[shirinshaikh]

Hi @Sven-iX That is correct. We need to use AND. However, with that I'm unable to specify for which task I need the completion date and which should have only start date.

Could you please suggest if there is a way I could do that?

[Sven-iX]

Pls share what type of report this is and your current filter

[shirinshaikh]

Sure. The report type is Task report.

Filter:

Project Portfolio ID = Roche AND Task Name = Asset Audit AND Task Status not equal to Cancelled / Complete

OR

Project Portfolio ID = Roche AND Task Name = Build AND Task Status not equal to Cancelled / Complete

[skyehansen]

I'm not sure I understand. Your result would be a list of "Asset Audit" and "Build" tasks, but only if task 1 is not done, and task 2 is started? 

[Sven-iX]

If I understand correctly 

• you report on tasks of projects in a portfolio
• you want to exclude all tasks that belong to a project in which
  • Asset Audit is not Cancelled/Complete AND 
  • Build is not Cancelled/Complete

That's not going to work I think - i tried an EXISTS block but that seems to break WF ... 
Makes sense: First you find all tasks in the portfolio and then you want to iterate over all the tasks in each project to determine whether that project is eligible to be shown based on 2 of its tasks. 

Without Fusion I don't see a way for you to "tag" projects for exclusion since your exclusion conditions are about 2 tasks - Using Fusion (or the API) you could set a custom field on the project (e.g. "Build and Audit in progress") and then in the filter you can filter for projects where that field is blank/false. 

[skyehansen]

Sven, I read it as "include" rather than "exclude", which is possible using exists. You would probably have to do two sets though, one for each task name.

[Sven-iX]

Skye - not sure this seems to talk about excluding projects (ie, their tasks)

I want to filter out projects where Task 2 is in progress and Task 1 is not complete

---

But regardless - how would you structure the EXISTS? 

I tried but failed. If it were a PROJ report - I get it, but this is a TASK report  

[skyehansen]

Exactly my confusion, Sven. In the original post, talked about exclusion. Yet, in one of the responses, they then said

---

Filter:

Project Portfolio ID = Roche AND Task Name = Asset Audit AND Task Status not equal to Cancelled / Complete

OR

Project Portfolio ID = Roche AND Task Name = Build AND Task Status not equal to Cancelled / Complete

---

(I think they meant "AND" rather than "OR")

So, who knows.

To answer your question though: let me include a sample piece of code below (please test thoroughly before use) and explanation.

name=asset audit
name_Mod=cicontains
actualCompletionDate_Mod=isnull

EXISTS:a:$$OBJCODE=TASK
EXISTS:a:projectID=FIELD:projectID
EXISTS:a:name=build
EXISTS:a:name_Mod=cicontains
EXISTS:a:actualStartDate_Mod=notnull

So: this block of code will first pull up "asset audit" tasks where the actual completion date is null. (task 1 end date is blank, from original post)

Then: it will only SHOW the asset audit task IF there's another task in the project called "build" where the actual start date is not null. (from the original post)

To show the build task, you would then set up a second set of filters that flips this block. (all prepended with OR -- pull up build tasks where actual start date is not null, and where there exists an asset audit task where actual completion date is null)

[Sven-iX]

Yep I get this. 

But I don't get how to use EXISTS to exclude / include projects whose tasks I am reporting on (that's my interpretation of the OP's post) 

Like "Show me tasks from all projects except from projects where Build and Audit are still in progress".

[skyehansen]

yep that's a weird question for sure, so I wish they would clarify. I mean, it might be a NOTEXISTS filter with an AND statement on the names, assuming that you're able to distill both tasks down to "not CPL" but I would hate to struggle through it and waste my time.

[shirinshaikh]

Hello,

To clarify, I'm trying to achieve the following. We need to show all tasks (grouped by project) where "Asset Audit" is not complete and "Build" has begun. Typically, Task for Build should not be started until the completion of Asset Audit task. Since we don't have enforced predecessors; we want to identify in the report of all such projects where build started even without Asset Audit being not completed.

Hope this helps clarify.

Please let me know if you have any questions. 

[Doug_Den_Hoed__AtAppStore]

Following,@shirinshaikh,

This sounds similar to our Quality Management Report solution. QMR has one or more "checklist" (aka Audit) tasks per project, with one or more pass/fail custom parameters per checklist. A Fusion job converts each such Fail into an Issue that must be resolved. All such issues on such a checklist task are intended to be resolved in order to then close the checklist task, with work on other (loosely) related tasks then proceeding.

Suspecting the many one-to-many relationships in QMR might be overkill for what you're after (e.g. if you have only one Audit and one Build Task per project), I'll pause there @skyehansen and @Sven-iX to continue to offer their guidance to you; but can come back to these QMR concepts later if there's interest.

Regards,

Doug

[skyehansen]

Sven, sounds to me like: if in Project ABC, we have 5 tasks, 2 of which are the Asset Audit and Build tasks, the OP would like to see all 5 tasks whenever Asset Audit has not completed and Build has begun. So, 2 back-to-back exists statements either assuming there is only ever one Asset Audit and one Build task per project or assuming it doesn't matter how many there are.

[skyehansen · Accepted solution]

Starting a new thread to address clarifications made by the OP

EXISTS:a:$$OBJCODE=TASK
EXISTS:a:actualStartDate_Mod=notnull
EXISTS:a:name=build
EXISTS:a:name_Mod=cicontains
EXISTS:a:projectID=FIELD:projectID
EXISTS:b:$$OBJCODE=TASK
EXISTS:b:actualCompletionDate_Mod=isnull
EXISTS:b:name=asset audit
EXISTS:b:name_Mod=cicontains
EXISTS:b:projectID=FIELD:projectID

[shirinshaikh]

This worked, thank you @skyehansen