Table showing Parent and subtasks based on Target start (in column)

Questions & Answers
1

Hi,

I would like to create a table that shows the following information.
Currently we have Tasks and under them sub-tasks are created for each team with Target start (current month +1). Each sub-tasks has their own statuses and component assigned. We would like to create a report that same format as below.

Tasks Subtasks with Current month + 1 Target start status Subtasks with Current month + 1 Target start components
Task1 Summary In Progress c1
On Hold c2

Thank you

2

Hi @Jim

Sorry for not answering sooner.

To create the table, that has the tasks and subtasks in it, please choose the Issue dimension in rows, under “All hierarchy level members” choose Sub-task at the Parent level.

Hierarchy level
Hierarchy level538×556 25.5 KB

Then under the “Drill into or expand” choose Sub-task under Sub-task level.
Drill into or expand
Drill into or expand520×750 33.7 KB

This will create the structure, tasks, and sub-tasks under them:

tasks with subtasks
tasks with subtasks478×506 5.92 KB

To get the subtask’s status based on the Target start that falls into next month, please use the formula:

CASE WHEN
 DateInPeriod(
   [Measures].[Issue target start],
   [Time].[Month].CurrentDateMember.NextMember
 )
THEN
 [Measures].[Issue status]
END

Please double-check the Issue target start name to see if it’s correct.

And a similar formula to create the component’s column based on the Issue Target start instead of [Measures].[Issue status] choose the [Measures].[Issue component] .

Kindly,
Ilze