Group all tasks by Custom field of parent task

Questions & Answers
,
1

Hi, @zane.baranovska or somebody else, could you please help me to solve the problem :sweat_smile:
I have a hierarchy in Jira, in which tasks on commands are linked to a task with type Task via the link type is blocked by. Using Advanced settings I have created a custom hierarchy:

#Try to bulid new hierarchy for Promo Task
[jira.customfield_promotask]
name = "Task"
inward_link = "is blocked by"
outward_link = "blocks"
issue_type = "Task"
dimension = true
update_from_issue_key = "parent_issue_key"

[[jira.issue_hierarchies]]
name = "Task"
all_member_name = "All Issues by Tasks"
levels = [
  {name="Task",key_column="customfield_promotask",issue_type="Task"},
  {name="Parent",key_column="subtask_parent_key"}, 
  {name="Sub-task",key_column="subtask_key"}
]

Now I need to build a report that uses the Customer Department field in the rows and the Time field in the columns. The report should output Hours spent on all tasks with Task type with Customer Department specified, add to them Hours spent on all linked tasks with a parent Task and their sub-tasks (regardless of which Customer Department they have), and also add to them Hours spent on those tasks on teams that have no parent Task and also their sub-tasks (but with Customer Department linked). To do this I use the following measure:

-- Суммируем часы для всех родительских задач с указанным Customer Department
Sum(
  Filter(
    Descendants([Issue].[Issue].Members, [Issue].[Issue]),
    -- Родительская задача без родителя (верхний уровень в иерархии)
    [Issue.Task].CurrentHierarchyMember.Parent IS [Issue.Task].CurrentHierarchy.DefaultMember
    AND
    -- Customer Department родительской задачи совпадает с Customer Department в строке таблицы
    [Issue].CurrentMember.Get('Customer Department') = [Customer Department].CurrentMember.Name
  ),
  [Measures].[Hours spent]
)

+
-- Суммируем часы для всех подзадач (кроме типа Task), у которых родительская задача с тем же Customer Department
Sum(
  Filter(
    Descendants([Issue].[Issue].Members, [Issue].[Issue]),
    -- Проверяем, что задача имеет родителя
    NOT [Issue.Task].CurrentHierarchyMember.Parent IS [Issue.Task].CurrentHierarchy.DefaultMember
    AND
    -- Customer Department родительской задачи совпадает с Customer Department в строке
    [Issue.Task].CurrentHierarchyMember.Parent.Get('Customer Department') = [Customer Department].CurrentMember.Name
    -- Исключаем задачи типа Task
    AND [Issue].CurrentMember.Get('Issue type') <> "Task"
  ),
  [Measures].[Hours spent]
)

+
-- Суммируем часы для подзадач (кроме типа Task) без родителя, у которых Customer Department совпадает с Customer Department в строке
Sum(
  Filter(
    Descendants([Issue].[Issue].Members, [Issue].[Issue]),
    -- Задача не имеет родителя
    [Issue.Task].CurrentHierarchyMember.Parent IS [Issue.Task].CurrentHierarchy.DefaultMember
    AND
    -- Customer Department задачи совпадает с Customer Department в строке
    [Issue].CurrentMember.Get('Customer Department') = [Customer Department].CurrentMember.Name
    -- Исключаем задачи типа Task
    AND [Issue].CurrentMember.Get('Issue type') <> "Task"
  ),
  [Measures].[Hours spent]
)

What am I doing wrong? Ideally I need to write in all Parent related tasks the value from the Customer Department field to some new custom field