Creating a filter for a project report that filters projects based on the completion date of a milestone task

[MatthewLi1]

For my project report I have all creative projects. I want users to be able to toggle using an additional filter to sort only projects which the CR0(Creative Review 0) is this month. Does anyone know if this is possible? I couldn't find anywhere on the community if anyone had found a solve for this or in the documentation if there was a way to do this.

I've created a column in the report that shows the CR0 date and if it's been completed. But due to the volume of projects that get pulled in with this report people are looking to filter the projects by CR0 dates in the next week and/or add groupings to show the week of the planned completion of the CR0 for the projects. Not sure if either of these things are possible. Below is the text mode code I'm currently using to show the CR0 Dates / status within a column for reference.

displayname=CR0
listdelimiter=<div>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF({milestone}.{sequence}=4&&{status}='NEW',{plannedCompletionDate},IF({milestone}.{sequence}=4&&{status}='CPL',"",IF({milestone}.{sequence}=4&&{status}='INP',"In Progress")))
valueformat=HTML

[rawvarun]

Hi,

You can apply the rule to your newly created column.

As you know, column rules are applied from the top down. Also, they will be combined, but will not overwrite each other.

[skyehansen · Accepted solution]

If you know how to make these tasks show up in a task report, copy and paste the filter here in this post, and I will show you how to convert it to a project filter by adding two lines to the task filter in the project report.

EXISTS:a:$$OBJCODE=TASK

EXISTS:a:projectID=FIELD:ID

EXISTS:a:___________

EXISTS:a:___________

EXISTS:a:___________

EXISTS:a:___________

EXISTS:a:___________

EXISTS:a:___________

[MatthewLi1]

Thanks Skye, this worked wonderfully!

Would I have to tweak this in order to use this to create a grouping to show the week of the planned completion of the CR0 for the projects?

[skyehansen]

hi Matthew, you cannot create a grouping in a project report that is based off any of the information you might receive out of a collection of tasks. The closest would be to group by a date that you would receive from the project, like either the project start or complete date, or a date found in the project custom form.

[MatthewLi1]

Got it, thanks

[KristenS_WF]

Regarding the filter, take a look at the "Reference a collection in the custom prompt of a report" section of this article.

I think it's helpful to build the filter in the report itself--testing to make sure it works--and then remove it and place the text mode in a custom prompt.  Folks could use the prompt to determine if they want to view just those projects with the CR0 date in the next week or all projects.