JavaScript calculated custom fields

eazyBI for Jira

If you would like to pre-calculate and import in eazyBI additional calculated custom fields then

• you can either use some add-on in Jira to create some Jira calculated and scripted custom fields,
• create a new calculated field specific to the particular eazyBI account with new calculated field,
• or you can define calculated custom fields using JavaScript in eazyBI advanced settings as described further (and then they will be available just in eazyBI and not in Jira).

If you have larger number of Jira issues then it is better to import calculated custom fields and not to create calculated members with complex MDX formulas. Calculated custom fields are pre-calculated just once during Jira import and then saved in the eazyBI database and then eazyBI queries will perform much faster using pre-calculated data.

Define calculated custom field in eazyBI advanced settings

You can define an additional custom field in the advanced settings.

For each new field, describe the following settings:

• Use your own customfield_name custom field name where name is some unique descriptive name of your additional calculated custom field. Best practices for naming the custom fields: keep it short and use just lowercase without underscores, special symbols, or camelCase.
• Add a name setting and specify a display name for this calculated custom field that will be visible in Jira import custom fields selection (these calculated custom fields will be always visible for any Jira project selection).
• Add javascript_code which will calculate and store in issue.fields.customfield_NAME result of the calculation. This JavaScript code can use the issue object in the same way as in the custom JavaScript code in Jira import options.

The order of the execution of JavaScripts for custom field calculations is not defined. We recommend avoiding reference from one JavaScript calculated custom field to another as it can give an unpredictable result. 

Here is an example of a calculated custom field "Issue summary". eazyBI imports issue key and summary as a name by default. The example code below will import the summary only as a property for an issue:

[jira.customfield_sumry]
name = "Summary"
data_type = "text"
javascript_code = '''
if (issue.fields.summary) {
  issue.fields.customfield_sumry = issue.fields.summary;
}
'''

How to make changes in advanced settings

There might be a situation when you want to adjust or change the JavaScript calculated custom field in advanced settings, but the changes are not working as expected. Whenever changing advanced settings for custom fields, you might want to perform double data import to ensure the correct outcome of changes.

1. In the import option, deselect the custom field from data import and import data. This action will clear the previous data and data structures (even if you see them as empty).
2. Wait for the data import to complete.
3. In the import option, select the custom field for data import and import data for the second time.

Validate the Javascript code before using it in the custom field definition

We highly recommend testing and debugging your JavaScript code before using it in data import. You can do this in Jira import options (from the main menu Source Data → Edit Jira import options → tab Additional options → Add custom JavaScript code). Paste your code in Custom JavaScript code and specify an issue key on which you would like to test your JavaScript code. Please note that in the field Custom javaScript code, you should paste only the javascript_code part (code between opening and closing quotation marks ''') without other parameters for advanced settings.

Test custom JavaScript code with several issues and validate if the newly defined custom field results appear as needed.

See a short video on validation here:

Custom calculated field as an interval dimension

You can define calculated custom fields also for import as an interval dimension. In this case, specify the following additional settings:

• dimension set to true
• In javascript_code set the value of the custom field as a number of seconds, minutes, hours or days. If the interval dimension is for a duration then this should be the number of time units between two dates. If the interval dimension is for an age then the number should be a Unix timestamp (number of seconds since January 1, 1970).
• In time_unit specify the corresponding time unit.
• In time_inverval specify either age or duration.
• In intervals and interval_unit specify the default interval ranges that should be used (which can be later changed in the Analyze tab).

The first is an example of "Age since updated interval" dimension which will show the age intervals (from the last update date until now) for unresolved issues:

[jira.customfield_updated_age_ts]
name = "Age since updated interval"
data_type = "integer"
dimension = true
javascript_code = '''
if (!issue.fields.resolutiondate) {
  issue.fields.customfield_updated_age_ts = Math.floor(Date.parse(issue.fields.updated) / 1000);
}
'''
time_unit = "seconds"
time_interval = "age"
intervals = "/10"
interval_unit = "days"

The following is an example of "Custom resolution interval" dimension which will show the duration intervals from the issue created date until the custom resolution date (instead of the standard Resolved date) for all issues having this custom resolution date. You may use any two dates instead of issue creation date and the custom resolution date. 

[jira.customfield_custom_resolution_interval]
name = "Custom resolution interval"
data_type = "integer"
dimension = true
javascript_code = '''
if(issue.fields.customfield_NNNNN) {
issue.fields.customfield_custom_resolution_interval = 
  (Date.parse(issue.fields.customfield_NNNNN) - Date.parse(issue.fields.created)) / 1000 / 60 / 60 / 24;
}
'''
time_unit = "days"
time_interval = "duration"
intervals = "8,15,31"
interval_unit = "days"

Cycle days

This example allows defining and importing cycle days based on a specified list of statuses. The JavaScript calculation will give you the total time spent in specified statuses of a cycle for resolved issues. Import cycle days as a set of measures and interval dimension for Cycle time distribution reports. 

[jira.customfield_cycledays]
name = "Cycle days"
data_type = "decimal"
dimension = true
measure = true
javascript_code = '''
if (issue.fields.resolutiondate) { // calculate for resolved issues only
  var transitto = Date.parse(issue.fields.created);
  // specify a list of statuses in cycle : 
  var statuslist = ["In Progress","In Review"];
  var timeInStatus = null;
  var transitfrom = null;
  // iterate through chengelog entries to get the status changes
  issue.changelog.histories.forEach(function(history){
    history.items.forEach(function(historyItem){
      if (historyItem.field == "status") {
        transitfrom = Date.parse(history.created);
        status = historyItem.fromString;
        // validate the status by status name
        if (statuslist.indexOf(status) > -1) {
          // add time when issue moves out of the status
           timeInStatus +=  (transitfrom - transitto) / 1000 / 60 / 60 / 24 ;
        }
        transitto = transitfrom;
      }
    });
  });
  if (timeInStatus) {
  issue.fields.customfield_cycledays = timeInStatus;
  }
} 
'''
time_unit = "days"
time_interval = "duration"
intervals = "/10"
interval_unit = "days"

After defining it, you should be able to import a new custom field via import options as measure and dimension.

Cycle date

This example allows defining and importing a cycle date based on a specified list of statuses. You can use the JavaScript calculation to import the first date of the cycle or the last date of the cycle (with a small modification). Import cycle date as measure and property. eazyBI will create a set of measures <with cycle date>, for example, Issues with cycle date, and property Issue cycle date. 

[jira.customfield_cycle_date]
name = "Cycle date"
data_type = "datetime"
measure = true
javascript_code = '''
var cycleStatuses = ["In Progress","In Review"];
cycleDate = null;
if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
  var histories = issue.changelog.histories; 
  for (var i = 0; i < histories.length; i++) { 
    var history = histories[i]; 
    if (history.items && history.items.length > 0) {
      for (var n = 0; n < history.items.length; n++) {
        var item = history.items[n]; 
        if (item.field == 'status') {
          movedToStatus = item.toString;
          if(cycleStatuses.indexOf(movedToStatus) > -1) {
            cycleDate = history.created;
            break ; //disable this line if the last date is needed. Break closes the iteration through status history
          }
      } } } } 
      issue.fields.customfield_cycle_date = cycleDate; 
}
'''

After defining it, you should be able to import a new custom field via import options as measure and property.

Calculated worklog custom field

If you store some structured information in worklog comments also known as worklog description (for example if the work is billable or non-billable, or if it is overtime) then you can use calculated worklog custom fields to extract this structured information as a separate dimension.

You define a calculated worklog field as a JavaScript calculated field and in addition:

• Specify worklog = true which will indicate that it is a worklog custom field.
• In javascript_code use the worklog object (which contains JSON representation of one worklog entry as in issue JSON representation). Worklog comment can be accessed as worklog.comment.
  The custom field value should be assigned to worklog.customfield_NAME.

Here is an example of a Worklog Comment dimension that will import the worklog comments/description + its id

[jira.customfield_wlcomment]
name = "Worklog Comment"
data_type = "string"
dimension = true
worklog = true
javascript_code = '''
if (worklog.comment) {
  worklog.customfield_wlcomment = worklog.comment+" ("+worklog.id+")";
}
else {
  worklog.customfield_wlcomment = "(no comment)"
}
'''

Import Worklog Comment as a dimension and then use it to analyze Hours spent measure.

Inherited field dimension

eazyBI analyzes data based on values on issues. However, in some cases, values in some fields are added for epics, parent issues, or on some other higher-level issues in the custom hierarchy. The reports will not give values from issues in epic, sub-tasks, or any lower-level issue when the dimension is used for filtering data. Issues should inherit these values during import. We support issue link field dimensions for some default fields (Fix Version, Label, Status, Resolution, Issue type, Priority).

For other default fields or for the custom fields (select list single select) it should be defined using calculated javaScript custom fields. The option is not supported for multiple value fields.

Define a new calculated JavaScript custom field. Use a formula to assign a value of the field you would like to inherit to this newly created custom field. Then you would like to use a parameter update_from_issue_key to specify from what hierarchy level you would like to pass down the values.

The example below will pass down values from Epic issue custom field to issues in epics:

[jira.customfield_NNNNN_e]
name = "Epic custom field name" 
data_type = "string"
dimension = true
update_from_issue_key = "epic_key"
javascript_code = '''
if(issue.fields.customfield_NNNNN ) {
   issue.fields.customfield_NNNNN_e = issue.fields.customfield_NNNNN;
}
'''

Please use a custom field ID instead of NNNNN in any place in the formula example above. You can set the desired name instead of Epic custom field name.

Here are options for default hierarchies:

• update_from_issue_key = "epic_key" if you would like to pass down values from Epic to issue in it.
• update_from_issue_key = "parent_issue_key" if you would like to pass down values from parent issues to sub-tasks
• update_from_issue_key = "jpoh_parent_X" if you are using the Advanced Roadmaps hierarchy and would like to pass down values in this hierarchy. Use a parent level number 1,2,3,.. instead of X counting parent levels on top of sub_tasks. 1 for story level (jpoh_parent_1), 2 for epic level (jpoh_parent_2), 3 ...
• If you are using a custom Issue hierarchy, you can address custom fields used for levels to pass down values in the custom hierarchy. For example, update_from_issue_key = "customfield_feature" for the Hierarchy with epics and Features to pass down values from feature issues.

Component

To import a component into a separate dimension you can use this calculated custom field definition in your eazyBI advanced settings

[jira.customfield_eazybicomponent]
name = "Component"
data_type = "string"
multiple_values = true
split_by = ","
dimension = true
javascript_code = '''
  issue.fields.customfield_eazybicomponent = issue.fields.components;
'''

After defining it, you should be able to import a new custom field via import options as dimension and property

Summary for Issues

To import a summary for Issue you can use this calculated custom field definition in your eazyBI advanced settings

[jira.customfield_sumry]
name = "Summary"
data_type = "text"
javascript_code = '''
if (issue.fields.summary) {
  issue.fields.customfield_sumry = issue.fields.summary;
}
'''

After defining it, you should be able to import a new custom field via import options as property for each issue. Then you can display this property for "Issue" dimension issue-level members.

Previous status

Sometimes you need to find the previous status for each issue and import it as property.

#previous status
[jira.customfield_prevst]
name = "Previous Status"
data_type = "string"
dimension = true
javascript_code = '''
var pst;
if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
  var histories = issue.changelog.histories;
  for (var i = 0; i < histories.length; i++) {
    var history = histories[i];
    if (history.items && history.items.length > 0) {
      for (var n = 0; n < history.items.length; n++) {
        var item = history.items[n];
        if (item.field == 'status') {
          pst = item.fromString;
        }
      }
    }
  }
}
issue.fields.customfield_prevst = pst;
'''

Description for Issues

To import a description for Issue you can use this calculated custom field definition in your eazyBI advanced settings

[jira.customfield_descr]
name = "Description"
data_type = "text"
json_fields = ['description']
javascript_code = '''
if (issue.fields.description) {
  issue.fields.customfield_descr = issue.fields.description;
}
'''

After defining it, you should be able to import new custom field via import options as property for each issue. Then you can display this property for "Issue" dimension issue-level members.

This approach would work only when eazyBI for Jira Cloud is used. For Server and Data Center plugin versions read here.

Last date when custom field changed

In eazyBI, it is possible to import single-value select custom field in a separate table with value changes by adding these advanced settings to your custom field (where you place your actual custom field ID instead of NNNNN)

[jira.customfield_NNNNN]
data_type = "string"
dimension = true
separate_table = true
changes = true

Then you could import this custom field as a dimension and import also value changes in eazyBI.

After that, you could update your eazyBI advanced settings with the following Javascript code that should return the last date when the custom field (use your actual field name in the code instead of <Customfield name> on Line15) was changed to a defined value (use your actual value in the code instead of <Value> on Line15).

Here is an example of a Jira single-value select custom field that will return the last date when a pre-defined custom field was set to the pre-defined value:

[jira.customfield_valuechange]
name = "Value change date"
data_type = "date"
measure = true
javascript_code = '''
var customfieldChangeDate;
if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
  var histories = issue.changelog.histories;
  for (var i = 0; i < histories.length; i++) {
    var history = histories[i];
    if (history.items && history.items.length > 0) {
      for (var n = 0; n < history.items.length; n++) {
        var item = history.items[n];
        if (item.field == '<Customfield name>' && item.toString == '<Value>') {
          customfieldChangeDate = history.created;
        }
      }
    }
  }
}
issue.fields.customfield_valuechange = customfieldChangeDate;
'''

Import Value change date as a measure and as a property.

Days for Assignee

To analyze how many days the issue was assigned for each assignee until the issue resolution date, you can precalculate the time between assignee transitions based on issue change history for each issue and then import this information as a measure Days for assignee. The measure is also associated with Time dimension (by transition from assignee date)

[jira.customfield_days_for_assignee]
name = "Days for assignee"
data_type = "decimal"
measure = true
multiple_dimensions = ["Time","Assignee"]
javascript_code = '''
var assigneehistory = new Array();
var datefrom = issue.fields.created;
var assignee = null;
resolution = false;
	issue.changelog.histories.forEach(function(history){
	 history.items.forEach(function(historyItem){
	   if (historyItem.field == "assignee") {
	      assignee = historyItem.from;
	      if (! resolution) {
	        dateto = history.created;
	        if(assignee){
				duration = (Date.parse(dateto) - Date.parse(datefrom)) / 1000 / 60 / 60 / 24;
				assigneehistory.push(dateto.toString().substr(0,10) + "," + assignee + "," + duration);
			}
			datefrom = dateto;
			assignee = historyItem.to;
		}
	   }
	    if (historyItem.field == "resolution") {
	      if (historyItem.to) resolution = true;
	      else resolution = false;
	    }
	 });
	});
 
	if (issue.fields.resolutiondate && (issue.fields.assignee || assignee)) {
	 if (!assignee) assignee = issue.fields.assignee.name;
		duration = (Date.parse(issue.fields.resolutiondate) - Date.parse(datefrom)) / 1000 / 60 / 60 / 24;
		assigneehistory.push(issue.fields.resolutiondate.toString().substr(0,10) + "," + assignee + "," + duration);
	}
	issue.fields.customfield_days_for_assignee = assigneehistory.join("\n");
'''

Use this measure in reports together with Assignee and/or Time dimension.

Days in Priority

To analyze how many days the issue was in each priority until the issue resolution date, you can precalculate the time between priority changes based on issue change history for each issue and then import this information as a measure Days in priority. The measure is also associated with the Time dimension (by transition from the priority change date).

[jira.customfield_prioritydays]
name = "Days in priority"
data_type = "decimal"
measure = true
multiple_dimensions = ["Time","Priority"]
javascript_code = '''
var priorityhistory = new Array();
var datefrom = issue.fields.created;
var priority = null;
resolution = false;
issue.changelog.histories.forEach(function(history){
  history.items.forEach(function(historyItem){
  if (historyItem.field == "priority") {
    priority = historyItem.fromString;
    if (! resolution) {
      dateto = history.created;
      if(priority){
        duration = (Date.parse(dateto) - Date.parse(datefrom)) / 1000 / 60 / 60 / 24;
        priorityhistory.push(dateto.toString().substr(0,10) + "," + priority + "," + duration);
      }
      datefrom = dateto;
      priority = historyItem.toString;
    }
  }
  if (historyItem.field == "resolution") {
    if (historyItem.to) resolution = true;
    else resolution = false;
  }
  });
});
// resolved issues without assignee change
issue.fields.customfield_prioritydays = priorityhistory.join("\n");
'''

Use this measure in reports together with Priority and/or Time dimension.

Comment when issue flagged

To import a comment when issue was flagged you can use this calculated custom field definition in your eazyBI advanced settings

#Last comement added with Flag
[jira.customfield_flcomment]
name = "Comment when flagged"
data_type = "string"
json_fields = ["comment"]
javascript_code = '''
if (issue.fields.customfield_NNNNN && issue.fields.comment && issue.fields.comment.comments
    && issue.fields.comment.comments.length > 0) {
  var commentsarray = issue.fields.comment.comments;
  for (var i = 0; i < commentsarray.length; i++) {
    var commenthist = commentsarray[i];
    if (commenthist.body && commenthist.body.indexOf("(flag) Flag added")>=0
       ) {   
         var flcomment = commenthist.body.split('Flag added\n\n')[1];
          break;
        }
  }
  if(issue.fields.customfield_NNNNN){
    issue.fields.customfield_flcomment = flcomment;}
}
'''

After defining it, you should be able to import new custom field via import options as property for each issue. Then you can display this property for "Issue" dimension issue-level members.

Last comment for issue

To import (as property) a body of the last comment added on the issue, you can use this calculated custom field definition in your eazyBI advanced settings

[jira.customfield_lastcommentt]
name = "Last comment text"
data_type = "text"
json_fields = ["comment"]
javascript_code = '''
if (issue.fields.comment && issue.fields.comment.comments && issue.fields.comment.comments.length > 0)  {
  var lastcomment = issue.fields.comment.comments[issue.fields.comment.comments.length-1];
  issue.fields.customfield_lastcommentt = lastcomment.body;
}
'''

After defining it, you should be able to import new custom field via import options as property for each issue. Then you can display this property for "Issue" dimension issue-level members.

Date for the last comment

To import a date when the last comment was added on issue you can use this calculated custom field definition in your eazyBI advanced settings

[jira.customfield_lastcommentdate]
name = "Latest comment date"
data_type = "datetime"
json_fields = ["comment"]
javascript_code = '''
var comments = issue.fields.comment.comments;
if (comments.length > 0) {
  var comment = comments[comments.length - 1];
  issue.fields.customfield_lastcommentdate = comment.created;
}
'''

After defining it, you should be able to import new custom field via import options as property for each issue. Then you can display this property for "Issue" dimension issue-level members.

A similar approach can be used to import the date when the first comment was added.

Multi-level cascading field

To import a multi-level cascading field from (Jira app provided by Sourcesense) you can try using this Javascript code (where you must use your multi-level custom field ID instead of NNNNN)

Code my vary depending on how many levels should be defined.

[jira.customfield_NNNNN]
data_type = "string"
dimension = true
levels = ["Level 1","Level 2","Level 3"]
split_by = ","
javascript_code = '''
var cfList = [];
if (issue.fields.customfield_NNNNN) {
  for (var i=0; i < issue.fields.customfield_NNNNN.length; i++) {
    var cfVal= issue.fields.customfield_NNNN[i].value;
    cfList.push(cfVal)
  }
}
if(cfList){
  issue.fields.customfield_NNNNN = _.uniq(cfList).join(",");
}
'''

After defining it in advanced settings, you can import new custom field via import options as dimension and property.

Status category change date for a new calculated field

The next approach would work only for Jira Cloud app!

There are some default Jira fields which are being returned in JSON view (via import options > additional options) but which can't be used by default when calculating other calculated fields using Javascript.

In such cases, the additional line Json_fields = ["Jira Field ID"] should be used in advanced settings to access the field during data import.

[jira.customfield_newfieldID]
name = "Status Category Change week"
data_type = "string"
dimension = true
json_fields = ["statuscategorychangedate"] #this line is mandatory
javascript_code = '''
if (issue.fields.hasOwnProperty("statuscategorychangedate")) {
  var sccd = new Date(Date.parse(issue.fields.statuscategorychangedate));
  sccd.setDate(sccd.getDate() - ((sccd.getDay() - 5 + 7) % 7));
  issue.fields.customfield_newfieldID = sccd.toISOString().substr(0, 10);
}
'''

The Javascript code returns a week start date. The 5 is the key: that is the day number for the starting day of the week. In this case, it is Friday (5). If you wanted your week to begin on Tuesday, use a 2.

After defining it in advanced settings, you can import new custom field via import options as dimension and property.

Watchers - users that watch imported issues

The next approach would work only for Jira Cloud app!

There are some default Jira fields which are being returned in JSON view (via import options > additional options) but which can't be used by default when calculating other calculated fields using Javascript.

In such cases, the additional line Json_fields = ["Jira Field ID"] should be used in advanced settings to access the field during data import.

[jira.customfield_watchusers]
name = "Watchers"
data_type = "string"
dimension = true
multiple_values = true
split_by = ","
json_fields = ["watches"] #this line is mandatory
javascript_code = ''' 
if(issue.fields.watches){
var result = 
  getDocument("/rest/api/latest/issue/" + issue.key +"/watchers");
var resultArray = [];
    if (result){
        for(x of result.watchers){
              resultArray.push(x.displayName)
        }
    issue.fields.customfield_watchusers = resultArray.join(",");
      }
}
'''

The Javascript code returns a comma separate list of watchers. 

Previous due dates

The next approach would work only for Jira Cloud app!

Due date change history is not being automatically imported in eazyBI yet, but you can use Javascript to access issue changelog and import previous due dates in eazyBI as string.

[jira.customfield_prevdd]
name = "Previous Due dates"
data_type = "text"
javascript_code = '''
var previousDuedate = new Array();
if (issue.changelog && issue.changelog.histories) {
//  first_change = true;
  for (var i=0; i < issue.changelog.histories.length; i++){
    var history = issue.changelog.histories[i];
    for (var a=0; a < history.items.length; a++) {
      var item = history.items[a];
      if (item.field == "duedate" && item.from) {
        previousDuedate.push(item.from);
        issue.customfield_1 = previousDuedate; // test
      }
    }
  }
}
if (previousDuedate) {
    issue.fields.customfield_prevdd = previousDuedate.join(",");
    issue.customfield_2 = issue.fields.customfield_prevdd; // test
}
'''

The Javascript code returns a comma separate list of previous due dates. 

After defining it in advanced settings, you can import a new custom field via import options as property.