Filtering a Project Report with Nested Lists

[RLepoudre]

So I have a project report that I have used nested lists to put certain task names that are the same across all projects as the columns of the report. 

My problem is figuring out how I can create a filter or prompt based on those tasks and when they are marked complete. 

Here is an example of the code being used in one of the columns of the report:

displayname=RFX
listdelimiter=<div>
listmethod=nested(tasks).lists
type=iterate
valueexpression=IF({name}="RFX", IF(CONTAINS("INP", {status}), "In Progress", IF(CONTAINS("CPL", {status}), "Complete", IF(CONTAINS("TIR", {status}), "Not Applicable", IF(CONTAINS("NEW", {status}), "", "")))), "")
valueformat=HTML

In this case, I'd be looking to somehow create a filter or prompt where it filters by RFX tasks marked Complete within the past two weeks. 

[skyehansen · Accepted solution]

From a TASK report, if I filter for task name contains RFX, status is Complete, and actual completion date in the past 2 weeks, here's the code I get:

name=RFX
name_Mod=cicontains
status=CPL
status_Mod=in
actualCompletionDate=$$TODAY
actualCompletionDate_Mod=between
actualCompletionDate_Range=$$TODAY-2w

Copy this code - this is what you'll be using in your project filter. The way I see it, you can go either way: a collections filter or an exists filter. Collections filters are documented here:

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/text-mode/reference-col...

You could start by trying to prepend your text mode (above) with "tasks:____" (omitting the quote marks and the underline)

Similarly with exists filters, documented here:

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/text-mode/create-comple...

Start with using these two lines in your project report:

EXISTS:a:$$OBJCODE=TASK

EXISTS:a:projectID=FIELD:ID

Then for your textmode block above, you could try and prepend each line with "EXISTS:a:_____"

(omitting the quote marks and the underline)

For the custom prompt, just follow the directions in this documentation:

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/create-manage-reports/a...

Take whatever textmode filter worked for you, and join it together in one giant line separating each phrase with ampersands.