Hi Anthony. This one's a doozy.
To recap, you're being asked to dynamically evaluate each Project's Tasks, figure out which Task is "next up" on the workplan, and then read ahead four additional Tasks to get a sense of what's coming...regardless of the dates in which those Tasks will occur (e.g. all within today, all within the next week, all within the next month, or "whenever"). If you go after it, I expect you'll need to consider:
Actual Start (not null) and Actual End (null) to pick off the current Task(s) in Progress
Handoff Date to see which Tasks can start, and (assuming the children are indented) Parent Task Handoff Dates (e.g. given Tasks A.1.a.i.x where each Task is indented from a summary perspective, when considering Task x, if its Parent Parent Parent Parent Parent Task A has a Handoff Date but is not yet started, then Task x should be shown, as should Tasks 1, a, and i by the same logic)
That said, if there is more than one Task A that is either started or ready to start, you might need to "branch" each and follow each of its 4 next children...hmmm....
This one seems to be a bridge to far for out of the box reporting. I suspect we could do it with a Magic Report , but instead, (as a reality check) I'd suggest you revisit the original requirements. Perhaps a dashboard with four reports showing Project Tasks "this week", "this month", "next 3 months", "next 6 months" -- using a filter to pick off the right Tasks in each report -- would suffice.
Regards,
Doug