Tracking time with a particular custom field value

Questions & Answers
1

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

2

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

3

Hi @janis.plume,

I’ve followed your advice and here’s the code I’ve come up with, which correctly generates the data I wanted to capture:

let updatesArray = [];

let history = issue.changelog.histories;
// temp is an object that will be pushed to updatesArray
// temp: {'Reason':'', 'StartDate':'', 'EndDate':''}
let temp = {
    reason : '',
    startDate : null,
    endDate : null
};

// each history item is an object which contains all the fields that were updated in that event
history.forEach(function (historyItem) {

    let items = historyItem.items;

    // We are going to dismiss the event that only 1 item was updated
    if (items.length !== 1) {

        // loop through the items array
        items.forEach(function (item) {

            // if the field is 'Reason for Holding', update the temp object with the reason
            if (item.field === 'Reason for Holding') {
                // update the temp object with the reason
                temp.reason = item.toString || item.fromString; // use whichever is not null
            }

            // if the field is 'status', and the toString or fromString is 'On Hold'
            if (item.field === 'status' && (item.toString === 'On Hold' || item.fromString === 'On Hold')) {
                // update the temp object with the startDate if the toString is 'On Hold'
                if (item.toString === 'On Hold') {
                    temp.startDate = new Date(historyItem.created);
                }
                // update the temp object with the endDate if the fromString is 'On Hold'
                if (item.fromString === 'On Hold') {
                    temp.endDate = new Date(historyItem.created);
                }
            }

        });

    }

    // if the temp object has a reason, startDate, and endDate, push it to the updatesArray, and reset the temp object
    if (temp.reason && temp.startDate && temp.endDate) {
        updatesArray.push(temp);
        // reset the temp object
        temp = {
            reason : '',
            startDate : null,
            endDate : null
        };
    }

    // if this is the last history item, push whatever is in the temp object to the updatesArray unless it is empty
    if (history.indexOf(historyItem) === history.length - 1) {
        if (temp.reason || temp.startDate || temp.endDate) {
            updatesArray.push(temp);
        }
    }
    
});
return updatesArray;

What I am now stuck on is getting this to display correctly within a report (just a table).
I’ve set the customfield to be type DateTime and allow multiple values separated by commas, but I am getting #Year errors on the import.

Issue EX-123 import failed: undefined method `year' for #<Array:0x398a4afc>
...

Can you advise at all on what I am doing wrong? What format does EazyBI expect these fields to be returned as?

Kind regards,
Harry