I am trying to get a report with the amount of time, in workdays, that a Jira issue has a particular field value while in a status. We have a status called ‘On Hold’ and while a ticket is in this status, the field “Reason for Holding” (single select list) has a value. I want to report on the amount of time that each ticket has in each of these “Reason[s] for Holding”. A ticket may go on hold more than once, and may have the same or a different reason each time. The report should then give the total time in each of these reasons.
I am currently using a custom measure for the workdays in status using this code:
-- days in transition status when issue was in this status in previous times IIF( -- if report uses Status dimension instead of Transition status it should work as well: [Status].CurrentHierarchyMember.Level.Name = "Status" and Not [Transition Status].CurrentHierarchyMember.Level.name = "Transition Status", ([Measures].[Workdays in transition status], [Transition Status].[Transition status].GetMemberByKey( [Status].CurrentHierarchyMember.Key )), [Measures].[Workdays in transition status]) + -- days since last transition to this status NonZero(SUM(Filter( Descendants([Issue].CurrentMember, [Issue].[Issue]), -- for unresovled issues only IsEmpty([Issue].CurrentHierarchyMember.Get("Resolved at")) AND IIF([Transition status].CurrentHierarchyMember.Level.Name = "Transition Status", [Transition status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1) AND IIF([Status].CurrentHierarchyMember.Level.Name = "Status", [Status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1) ), CASE WHEN [Measures].[Issues history] > 0 THEN DateDiffWorkdays( [Measures].[Issue status updated date], Now() ) END ))
This is only giving me the time for one of the reasons, even when there are known to be multiple.
Can anyone give me some advice on how to progress this, as I am stumped?