Tracking time with a particular custom field value

Hello,
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?
Thanks,
Harry

Hi,

This use case does not have a complete solution with an MDX formula because there can be several transitions to On Hold status.

A solution is to implement a new measure with the Javascript-calculated field.

I do not have such an example precisely, but please, check the documentation with some examples of how to do that here:
https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-custom-fields/javascript-calculated-custom-fields

Kindly,
Janis, eazyBI support