Grouping a project report by certain task criteria?

[-Jaye]

I've built a project report that leverages EXISTS in the filter to pull in only projects that have design milestones with a planned start date within a specific range (this week, next week, or 2 weeks out). I then have three columns that each use collections to list the milestones with a planned start date within a breakdown of that range (one column for this week, one for next week, one for 2 weeks out).

What I am looking for next is a way to group these projects based on those date ranges - so grouping 0 would read "Projects with design milestones starting this week", grouping 1 would read "Projects with design milestones starting next week", etc. I haven't explored milestone reports or using text mode on them but I believe I require a project report as I need a project-level custom field to be editable within the report.

One caveat I can think of, if this is even possible, is that one project may have design milestones starting this week AND next week - but, if so, I would want it to only show under the "earliest" grouping if that makes sense. So if Project 123 has design milestones beginning this week AND next week, it would appear in the "Projects with design milestones starting this week" grouping.

Is there any way to make this work? Can I ask the grouping to look "down" at tasks in any way? Hopefully this makes sense and please let me know if I can explain anything further.

FILTER

EXISTS:1:$$EXISTSMOD=EXISTS

EXISTS:1:$$OBJCODE=TASK

EXISTS:1:milestoneID=6113ddba004d21e2d77f5238f2388801 6113ddd9004d43d12c1272734be34a4a 612940640075e731c8e133390177bf33

EXISTS:1:plannedStartDate=$$TODAYbw

EXISTS:1:plannedStartDate_Mod=between

EXISTS:1:plannedStartDate_Range=$$TODAYe+2w

EXISTS:1:projectID=FIELD:ID

EXISTS:1:roleID=6064a8f100263c560b6c374e1ab65a8a

EXISTS:1:roleID_Mod=notin

EXISTS:1:status=NAA CPL NAL

EXISTS:1:status_Mod=notin

status=CUR PLN

status_Mod=in

COLUMN EXAMPLE

displayname=Design milestones starting this week

listdelimiter=<p>

listmethod=nested(tasks).lists

textmode=true

type=iterate

valueexpression=IF(CONTAINS("design",{milestone}.{name}),IF({plannedStartDate}>$$TODAYbw,IF({plannedStartDate}<$$TODAYew,IF({status}="INP"|{status}="NEW",CONCAT({milestone}.{name}," - ",{plannedStartDate}," (",ROUND({percentComplete},0),"%)")))))

valueformat=HTML

[skyehansen · Accepted solution]

I think the only way to make this work would be as a dashboard with multiple reports, say the topmost report is the one for design milestones starting this week, the second report with design milestones starting next week, and so on.

[-Jaye]

Sometimes we just need another set of eyes for the most simple answer. Thanks Skye, the idea of separating this out into three reports on a dashboard actually did not come to my mind! At the very least that will be more digestible for our leaders.