Through the help of the community I was able to see a roll up of our hours spent by Epic with the Tasks underneath them. I now have a slightly different request that I haven’t found any community articles on.
We’d like to see the hours rolled up to the Epic’s status. So basically the epic has statuses such as:
Pre-Implementation
File Import
File Review
Complete
The Tasks that are under the epic have statuses:
To Do
In Progress
Complete
We’d like it so that the hours logged to the Task can be displayed under the status that the Epic is in. Any ideas on how to accomplish this?
To have the Hours spent Rolled up by shown by Epic status, you need to define the Epic Status dimension. You can do that in Advanced Settings: Advanced settings
Our documentation has the example - Epic status. The Advanced settings for Epic Status should look something like this:
[[jira.issue_link_field_dimensions]]
name = "Epic Status"
source_dimension = "Status"
issue_id_column = "epic_id"
group = "Linked issue dimensions"
After defining the Epic Status in the Advanced settings, please go to the JIRA Import options in Additional tad and choose the Epic Status to perform the data import: Issue link field dimensions.
Hi there,
I have another question on this, if you can help me. I am actually looking for this roll up by Transition Status. Is there a way to build Transition Epic Status? If so, can you point me in the right direction for that? I’d like to historical, not just current state.
Hi @ilze.mezite - I have another question on this, if you can help me. I am actually looking for this roll up by Transition Status. Is there a way to build Transition Epic Status? If so, can you point me in the right direction for that? I’d like to historical, not just current state.
It is quite a complex solution.
My wonderful college Mārtiņš answered you privately with this solution.
To find the hours logged from children of the epic while the epic was in particular transition status.
This calculated measure expects that epic level from the “Issue” dimension (issue.epic hierarchy) is used in report rows. See the picture below:
Sum(
-- iterates through all transition statuses
DescendantsSet([Transition Status].CurrentHierarchyMember,[Transition Status].CurrenthierarchyMember.levels("Transition Status")),
Sum(
--set of epics in epic hierarchy
DescendantsSet(
[Issue.Epic].CurrentMember,
[Issue.Epic].[Epic]),
-- filters epics with transition to status
CASE WHEN
NOT IsEmpty(
DefaultContext(
([Measures].[Transition to status first date],
[Issue.Epic].CurrentMember,
[Transition Status].CurrenthierarchyMember)
))
THEN
-- get days from epic status started / ended or till today
Sum(
{
-- epic status started
[Time].[Day].DateMember(
DefaultContext(
([Measures].[Transition to status first date],
[Issue.Epic].CurrentMember,
[Transition Status].CurrenthierarchyMember)
))
-- set of all days between start date and end date
:
-- epic status ended
[Time].[Day].DateMember(
CASE WHEN
[Issue.Epic].CurrentMember.GetNumber('Status ID') = [Transition Status].CurrentHierarchyMember.Key
THEN
DateParse('Tomorow')
ELSE
TimestampToDate(DefaultContext(
([Measures].[Transition from status last timestamp],
[Issue.Epic].CurrentMember,
[Transition Status].CurrenthierarchyMember)
))
END
).PrevMember
},
-- hours spent with subtasks from epic issues / ignoring their transition status
(
[Measures].[Hours spent with sub-tasks],
[Epic Link].[Epic].GetMemberByKey(
[Issue.Epic].CurrentMember.key
),
[Issue.Epic].CurrentHierarchy.DefaultMember,
[Transition Status].CurrentHierarchy.DefaultMember
)
)
END
)
)
+
(
[Measures].[Hours spent],
[Issue Type].[Epic]
)
The formula finds the first and last transition, which can be slightly off if epic is going through the same status multiple times.
Thank you @ilze.mezite , I tried to put this Measure in but it keep timing out even with only 1 field (Issue) as a row, no pages, and no additional columns. Any recommendations? I am happy to work through a support ticket if that is the more appropriate channel.
