How to pull grandchildren into report

[thbrandtSU]

I have a task report and am able to pull in the first level of children with a list:

displayname=Children Tasks
listdelimiter=<br>
listmethod=nested(children).lists
type=iterate
valueexpression=CONCAT({name}, " - Duration: ", {duration}/480, " days, Due Date: ", {plannedCompletionDate})
valueformat=HTML

How can I get at least one more level down?

Is there something like: nested(children.children).lists?

[Sven-iX]

To my knowledge you can't - simply because children is a collection. And indeed, each of its members may have their own collection of children but there is no way to nest iterations. 

I try to stay away from iterating over tasks/issues in this way because while technically doable, depending on the number of tasks/issues this is a resource intensive report and may become slow. 

It looks like you're trying to emulate the project task view - why? 

[thbrandtSU]

Thanks. Not being able to might actually help some process
The reason is the report is filtering by for specific tasks(by looking or a single form attached to the task) across projects and providing a summary for leadership who 1) do not typically go into projects and 2) need a peak for these tasks across several projects.

I'm open to another way if there are thoughts.

[Sven-iX]

Hmmm. 
So my first thought is that if leadership wants a summary - looking at grandchildren ain't it So I would ask exactly what they are trying to read out.
Thinking, if I need to know about TaskA (status, dates) and want to scan the child tasks, would it work to list all non-parent tasks under Task A, and group by the A tasks?

If that might fly, I think I can help. 

[thbrandtSU]

The issue is the way some projects have been set up. "Grouping" tasks were added which make the tasks I am trying to expose grandchildren. And these are tasks that tell the story.

Below is an example of the project structure. Generally we have several project phases, one being Production, under which every item needing produced gets added as a "deliverable" with a form from a template. Each deliverable has its own set of tasks needing to be done and assigned.

Project Summary

- Project Phase

-- Deliverable (this is the level I am pulling in)

--- Concepting

---- Task 1 (this level is what needs to be shown)

---- Task 2

--- Production

---- Task 3

---- Task 4

--- Post-Production

---- Task 5

---- Task 6

The report we are building is a view of all Deliverables grouped by Project. There is desire to look at the individual tasks and summary timeline (duration and due date) making up each deliverable.

TBH, these "grouping" tasks are a bit of a deviation from our templates, so I think a conversation could be had to say "we get task groups or the desired reporting, but not both".

And to your earlier comment, maybe the recommendation would be for the team leader to look into the project and work on better filters and views to lessen what is seen.

[Sven-iX]

Hey @thbrandtSU 
First of all hats off to a cogent description of the requirement, thank you! 

I think this should do the trick: 

• Task report filters
  • any project filters (status=CUR etc) 
  • numberOfChildren=0
  • parent:parent:templateTaskID =    or parent:parent:milestoneID = {the deliverable task}
• Group by
  

  group.0.valueexpression="Project: "+{project}.{name}+" - " + {parent}.{parent}.name + ", due: "+{parent}.{parent}.{plannedCompletionDate}
  group.0.valueformat=HTML​

• Make the task name column 
  

  valueexpression={parent}.{name}+": "+{name}
  valueformat=HTML
  link.valuefield=ID             //links to the specific task; you can also say 
  ​

Unless I have typos this should give you something like 

[ Project: Cool project - Deliverable name, due MM/DD/YYYY                                                 ] < group bar

- Concepting: Task 1

- Concepting: Task 2

- Production: Task 1

- Production: Task 2

- Post-Production: Task 1

- Post-Production: Task 2

[ Project: Next project - Deliverable name, due MM/DD/YYYY                                                 ] < group bar

.....

[Sven-iX]

@thbrandtSU addendum

Just noticed that filtering to parent:parent... won't work (anymore) 
So you'll have to tag each of the tasks (hidden admin field) with a calc field we can use in the report

Filter

DE:IsProduction=true
numberOfChildren=0
numberOfChildren_Mod=eq
projectID=xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
projectID_Mod=in

[Sven-iX]

@thbrandtSU did you get it to work? 

[thbrandtSU · Accepted solution]

Hi Sven,

Sorry for the delay, I had to pause and come back to this.

I really appreciate your suggestions. I ended up not replicating exactly what you shared, though your concept inspired a similar solution.

One concern I had was in case a task was buried more or even less, a hardcoded {parent}.{parent}.{name} would break.

So I ended up adding a calculated field to be placed on any task where work is being assigned (not a grouping task). This field checks its ancestry by looking for a Job Number field which is present on the main Deliverable form.

It looks for the name up 4 parents, so great-great-grand-parent

IF(!ISBLANK({parent}.{DE:Job Number}), {parent}.{name},
IF(!ISBLANK({parent}.{parent}.{DE:Job Number}), {parent}.{parent}.{name},
IF(!ISBLANK({parent}.{parent}.{parent}.{DE:Job Number}), {parent}.{parent}.{parent}.{name},
IF(!ISBLANK({parent}.{parent}.{parent}.{parent}.{DE:Job Number}), {parent}.{parent}.{parent}.{parent}.{name},
"Too many sub-tasks")))))

Of course, now we can pull this field into reporting: "give me all tasks where DE:DeliverableAncestor is not blank and group them by that value".

In my early tests, it seems to be working.

FWIW, I have strayed away from using forms with only calculated fields due to not wanting to confuse PMs with remembering to add various utility forms and also feeling like clutters things up. That said, you have opened my eyes some to the potential value add.

Additionally, we have had some other talks as to what exactly needs to be reported upon and want to explore using a "Milestone Form" which would identify particular tasks as dates to pay attention to as opposed to just pulling in every task with the above Deliverable Ancestor form.

Overall, both methods head one of your initial cautions to be more intentional about what is pulled in vs queries on queries.

Thanks again for "rubber ducking" this with me.

[Sven-iX]

Man - I had to google "rubber ducking" - thank you for teaching me something new : )

Love the checking 4 levels - well done. 
On calc fields - if they're not needed by the regular users, I stick them into their own section and make that section admin-only. So no need for other forms, they're essentially hidden fields unless you're a WF admin. 

This here is key: "be more intentional about what is pulled in vs queries on queries." - WF reporting is not SQL, and that forces us to be more mindful of how we present the data. 

Great job working through this, love you got what you need! 

[jon_chen]

Did you find the suggestions helpful? If you need more information, please let us know. If a response resolved your issue, kindly mark it as correct to help others in the future. Alternatively, if you discovered a solution on your own, we'd appreciate it if you could share it with the community. Thank you @thbrandtSU!