Jira calculated and scripted custom fields
eazyBI for Jira

In Jira, you can display data based on computed values to support any number of uses cases. On Atlassian Marketplace you can find several Jira apps that let you create calculated custom fields for issues like Scriptrunner for Jira, Jira Misc custom fields, and others.

This page explains the main principle of calculated custom fields and how to import them in eazyBI, and also contains a few examples of ScriptRunner for Jira and Jira Misc Custom Fields.

These custom fields can be used for dynamic calculations and extended use-cases.

On this page:

Single-issue picker and multi-issue picker field import

For Jira single-issue picker field, the additional definition in advanced settings are required (where the corresponding custom field ID replaces NNNNN).

[jira.customfield_NNNNN]
data_type = "string"
dimension = true
properties_from_dimension = 'Issue'

For Jira multi-issue picker field, use this definition to import all values from the custom field as separate members in a new dimension.

[jira.customfield_NNNNN]
data_type = "string"
dimension = true
multiple_values = true
split_by = ","
properties_from_dimension = 'Issue'

Create a new calculated field in Jira

Create a new calculated custom field using your preferred app for Jira. Note that it would require Jira system administrator access rights to create new custom fields.

Please read these documentation pages to learn more about how to create new calculated custom fields for Jira using two popular plugins.

ScriptRunner for Jira documentation
All custom field calculation formulas and advanced settings mentioned in the examples below are supported by Adaptavist ScriptRunner for JIRA (Data Center) and will NOT work in ScriptRunner for JIRA Cloud.

Jira Misc Custom Fields documentation
All custom field calculation formulas mentioned in the examples below are supported by Jira Misc Custom Fields version 1.x. They might not work if using Jira Misc Custom Fields version 2.0.0 or later because of changes in Jira Misc functionality.
The principles and advanced settings importing Jira Misc fields into eazyBI are the same for all Jira Misc app version.

After creating a new custom field you should re-index Jira as suggested. Visit issue pages and see if your calculated text field is returning the expected value. If you do not see the custom field on Default Screen it may be caused by exception in the formula.

Please see Jira log file (log/atlassian-jira.log in the home directory or console log) if there are any exceptions logged.

Enable calculated custom field in eazyBI advanced settings

By default, eazyBI detects standard custom fields for issues and you can import them in the cube but calculated custom fields are not recognized automatically for import. These fields should be enabled for import in advanced settings.

If you will define Calculated Date/Time Field or Transition Date/Time Field then they will be recognized by eazyBI as additional custom fields and you will be able to select them in Source Data tab in application import custom field selection.

If you defined Calculated Number Field or Calculated Text Field then you will need to add this field to eazyBI advanced settings. In our "Creator" example at first, you need to find out custom field ID. If you go to Edit Custom Field Details screen then in URL you will see

.../secure/admin/EditCustomField!default.jspa?id=NNNNN

where NNNNN is custom field ID. Then in eazyBI advanced settings add

[jira.customfield_NNNNN]
data_type = "string"

where NNNNN is replaced by corresponding custom field ID.

Import custom field into eazyBI

After the calculated custom field is created and eazyBI advanced settings are updated, you should see your new calculated custom field name in the Source Data tab in application import custom field selection. Select the Import as property option for this custom field. After import, you should see additional "Issue Creator" calculated measure in Measures / Calculated members / Issue properties section and you can use it in reports where you have Issue dimension on rows and expanded to detailed issues.

And also you can access this custom field with [Issue].CurrentMember.get("Creator") in your other eazyBI calculation formula.

Examples

We have gathered a few scripted field examples but you can find more in:

Scriptrunner library: https://library.adaptavist.com/search?page=1&apps=scriptrunner&products=jira&features=script-fields
Use cases for Jira Misc custom fields: https://innovalog.atlassian.net/wiki/spaces/JMCF/pages/141892926/Use+cases+for+custom+fields=

Environment

ScriptRunner for Jira

This Text Field (multi-line) formula returns the Environment of an Issue in the scripted field.

def environment = issue.environment;
if (!environment){
    return;
}
else{
    environment;
}

Jira Misc Custom Fields

This Custom Text field formula returns the Environment of an Issue in the calculated text field.

<!-- @@Formula:
environment = issue.getIssueObject().environment;
if (environment == null) return null;
environment;
-->

eazyBI Advanced settings

To import this calculated custom field as a property of an issue, add the following advanced settings (by replacing NNNNN with actual field ID from )

[jira.customfield_NNNNN]
data_type = "text"

Next, you could select this field via eazyBI import settings to import as the property of an issue.

Now you could find a new measure "Issue Environment" in the "Measures" dimension that will show the description of an issue for the "Issue" dimension (issue level) members.

Due date changes

ScriptRunner for Jira

This Text Field (multi-line) formula will return a string of dates and counters when the due date was changed.

import java.text.SimpleDateFormat;
import org.apache.commons.lang.StringUtils;
import com.atlassian.jira.component.ComponentAccessor;

def dateStrings = new ArrayList();
def dateFormat = new SimpleDateFormat("yyyy-MM-dd");
def settime = issue.getCreated().getTime();
def items = ComponentAccessor.getChangeHistoryManager().getChangeItemsForField(
  issue, "duedate"
);
for (item in items) {
    def fromval = item.getFrom();
    if (fromval != null) {
        def from = dateFormat.parse(fromval).getTime();
        dateStrings.add(dateFormat.format(settime) +",1") ;
    }
    settime = item.getCreated().getTime();
}
if (!issue.dueDate) {
    return
}
else{
    dateStrings.add(dateFormat.format(settime) + ",1") ;
}
 
if (dateStrings.isEmpty()){
    return
}
else{
    StringUtils.join(dateStrings, "\n");
}

Jira Misc Custom Fields

This Custom Text Field formula will return a string of dates and counters when the due date was changed.

<!-- @@Formula:
import java.text.SimpleDateFormat;
import org.apache.commons.lang.StringUtils;
import com.atlassian.jira.component.ComponentAccessor;
dateStrings = new ArrayList();
dateFormat = new SimpleDateFormat("yyyy-MM-dd");
long settime = issue.get("created").getTime();
items = ComponentAccessor.getChangeHistoryManager().getChangeItemsForField(
  issue.getIssueObject(), "duedate"
);
for (item : items) {
	fromval = item.getFrom();
	if (fromval != null) {
		from = dateFormat.parse(fromval).getTime();
		dateStrings.add(dateFormat.format(settime) +",1") ;
	}
	settime = item.getCreated().getTime();
}
if (issue.get("duedate") != null) {
	dateStrings.add(dateFormat.format(settime) + ",1") ;
}

if (dateStrings.isEmpty())  return null;
StringUtils.join(dateStrings, "\n");
-->

eazyBI Advanced settings

To add this field import as a measure "due date change", add the following to advanced settings where NNNNN is replaced by corresponding scripted custom field ID.

[jira.customfield_NNNNN]
data_type = "integer"
measure = true
split_by = ","
multiple_dates = true

After selecting via import options and importing as measure you can select your new measure "Due date change" in a report and use it together with the "Time" dimension

Numeric measure with multiple dates

ScriptRunner for Jira

This is an example of how to build a custom field with a formula that returns multiple numeric measure values for one issue but split by several dates.

This Daily estimates example formula checks if the issue has Original estimated time and due date. If yes then it will distribute estimated hours starting from the due date and backward and using no more than 8 hours per day. For example, if the issue had a due date March 14 and had 36.5 estimated hours then calculated field will have the following value (each date on a separate line and the first value is the date in yyyy-mm-dd format, then a comma and then the number of hours):

2014-03-14,8
2014-03-13,8
2014-03-12,8
2014-03-11,8
2014-03-10,4.5

This is Text Field (multi-line) formula for Daily estimates custom field that should be used for this example:

import org.apache.commons.lang.StringUtils;
import java.text.SimpleDateFormat;
import java.util.Calendar;
 
def dateStrings = new ArrayList();
def dateFormat = new SimpleDateFormat("yyyy-MM-dd");
 
def dueDate = issue.dueDate;
def estimate = issue.getOriginalEstimate();

if (dueDate && estimate && estimate > 0) {
  def calendar = Calendar.getInstance();
  calendar.setTime(dueDate);
  def hours = estimate / 3600.0;
  def dateHours	
  while (hours > 0) {
    dateHours = hours > 8 ? 8 : hours;
    dateStrings.add(
      dateFormat.format(calendar.getTime()) + "," +
      String.valueOf(dateHours)
    );
    hours -= dateHours;
    calendar.add(Calendar.DATE, -1);
  }
}
if (!dateStrings) {
	return;  
} 
else{
    StringUtils.join(dateStrings, "\n");
}

Jira Misc Custom Fields

This is an example of how to build a custom field with a formula that returns multiple numeric measure values for one issue but split by several dates.

This Daily estimates example formula checks if the issue has Original estimated time and due date. If yes then it will distribute estimated hours starting from the due date and backward and using no more than 8 hours per day. For example, if the issue had a due date March 14 and had 36.5 estimated hours then calculated field will have the following value (each date on a separate line and the first value is the date in yyyy-mm-dd format, then a comma and then a number of hours):

2014-03-14,8
2014-03-13,8
2014-03-12,8
2014-03-11,8
2014-03-10,4.5

This is Custom Text Field formula for Daily estimates custom field that should be used for this example:

<!-- @@Formula:
import org.apache.commons.lang.StringUtils;
import java.text.SimpleDateFormat;
import java.util.Calendar;

dateStrings = new ArrayList();
dateFormat = new SimpleDateFormat("yyyy-MM-dd");

dueDate = issue.get("duedate");
estimate = issue.get("timeoriginalestimate");

if (dueDate != null && estimate != null && estimate > 0) {
  calendar = Calendar.getInstance();
  calendar.setTime(dueDate);
  hours = estimate / 3600.0;

  while (hours > 0) {
    dateHours = hours > 8 ? 8 : hours;
    dateStrings.add(
      dateFormat.format(calendar.getTime()) + "," +
      String.valueOf(dateHours)
    );
    hours -= dateHours;
    calendar.add(Calendar.DATE, -1);
  }
}

if (dateStrings.isEmpty()) return null;
StringUtils.join(dateStrings, "\n");
-->

eazyBI Advanced settings

In advanced settings add the following settings for this custom field:

[jira.customfield_NNNNN]
data_type = "decimal"
measure = true
multiple_dates = true

In the eazyBI Source Data tab import this custom field both as measure and as property.

Original estimated hours history

ScriptRunner for Jira

This example is how to import changes of field "Original estimated hours history" in eazyBI as a separate measure. In order to achieve that, there should be a new Text Field (multi-line) of the scripted custom field created. This is the formula for the Original estimated hours history custom field.

import org.apache.commons.lang.StringUtils;
import com.atlassian.jira.component.ComponentAccessor;
import java.text.SimpleDateFormat;

def dateFormat = new SimpleDateFormat("yyyy-MM-dd");
def values = new ArrayList();
def items = ComponentAccessor.getChangeHistoryManager().getChangeItemsForField(
  issue, "timeoriginalestimate"
);
for (item in items) {
  def to = item.getTo();
  if (to != null) {
    def toNumber = Float.parseFloat(to)/3600;
    values.add(dateFormat.format(item.getCreated()) + "," +toNumber. toString());
  }
}
if (!values) {
	return;  
} 
else{
    StringUtils.join(values, "\n");
}

Jira Misc Custom Fields

This example is how to import changes of field "Original estimated hours history" in eazyBI as a separate measure

In order to achieve that, there should be a new text type of MISC calculated custom field created.

This is the formula for Original estimated hours history custom field that you could put in the field description

<!-- @@Formula:
import org.apache.commons.lang.StringUtils;
import com.atlassian.jira.component.ComponentAccessor;
import java.text.SimpleDateFormat;

dateFormat = new SimpleDateFormat("yyyy-MM-dd");
values = new ArrayList();
items = ComponentAccessor.getChangeHistoryManager().getChangeItemsForField(
  issue.getIssueObject(), "timeoriginalestimate"
);
for (item : items) {
  to = item.getTo();
  if (to != null) {
    toNumber = Float.parseFloat(to)/3600;
    values.add(dateFormat.format(item.getCreated()) + "," +toNumber. toString());
  }
}
if (values.isEmpty()) return null;
StringUtils.join(values, "\n");
-->

That should retrieve all the changes for field "Original estimated hours" and return them in string

eazyBI Advanced settings

After that, in advanced settings add the following settings for this custom field where you place your actual field ID instead of NNNNN.

[jira.customfield_NNNNN]
data_type = "decimal"
measure = true
multiple_dimensions = ["Time"]
split_by = ","

Then In the eazyBI Source Data tab import this custom field as measure and use it together with Time Dimension in your eazyBI reports.

Original estimated hours change

ScriptRunner for Jira

This example is how to import changes of field "Original estimated hours history" in eazyBI as a separate measure. In order to achieve that, there should be a new Text Field (multi-line) of the scripted custom field created. This is the formula for the Original estimated hours history custom field.

import org.apache.commons.lang.StringUtils;
import com.atlassian.jira.component.ComponentAccessor;
import java.text.SimpleDateFormat;
import com.atlassian.jira.issue.IssueImpl;

def dateFormat = new SimpleDateFormat("yyyy-MM-dd");
def values = new ArrayList();
def items = ComponentAccessor.getChangeHistoryManager().getChangeItemsForField(
  issue, "timeoriginalestimate"
);
def created_d = issue.created;
def curest = issue.getOriginalEstimate();
def firstchange = true;
def removal = true;
def toNumber = 0.00;

if(curest && !items)
{
    	 toNumber = curest/3600;
    values.add(dateFormat.format(created_d) + "," +toNumber.toString());   
}
//System.out.println(curest);
for (item in items) {
  def from = item.getFrom();
  def to = item.getTo();
  if (to != null) {  
      if(from != null)
      {
       if(firstchange)
          {
	 toNumber = Float.parseFloat(from)/3600;
    values.add(dateFormat.format(created_d) + "," +toNumber.toString());
          }
          firstchange = false;
     toNumber = Float.parseFloat(to)/3600-Float.parseFloat(from)/3600;
    values.add(dateFormat.format(item.getCreated()) + "," +toNumber.toString());
      }
  }
    if(to == null) {
      if(from != null) {
      
          toNumber = Float.parseFloat(from)/3600;
    values.add(dateFormat.format(item.getCreated()) + "," +toNumber.toString());
      
          if(removal){
     toNumber = 0-Float.parseFloat(from)/3600;
    values.add(dateFormat.format(item.getCreated()) + "," +toNumber.toString());
      }
      }
  };
}
if (!values) {
	return;  
}    
else{
    StringUtils.join(values, "\n");
}

That should retrieve all the changes for field "Original estimated hours" and return them in string

eazyBI Advanced settings

After that, in advanced settings add the following settings for this custom field where you place your actual field ID instead of NNNNN.

[jira.customfield_NNNNN]
data_type = "decimal"
measure = true
multiple_dimensions = ["Time"]
split_by = ","

Then In the eazyBI Source Data tab import this custom field as a measure and use it together with Time Dimension in your eazyBI reports.

Amount paid (ScriptRunner for Jira)

ScriptRunner for Jira

This Custom Text field formula returns the amount paid of an Issue in the calculated text field.

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.Issue

// sum up the values of this custom field
final String customFieldName = "Amount Paid"

def subtasks = issue.subTaskObjects

// if the issue doesn't have any sub-tasks or is a subtask itself then no need for action
if (!subtasks) {
    return
}

def firstSubtask = subtasks.first()
def customField = ComponentAccessor.customFieldManager.getCustomFieldObjects(firstSubtask).findByName(customFieldName)
if (!customField) {
    log.info "Custom field with name $customFieldName is not configured for issue type $firstSubtask.issueType.name and project $firstSubtask.projectObject.key"
    return null
}

subtasks.sum { Issue it -> it.getCustomFieldValue(customField) ?: 0 }

eazyBI Advanced settings

To import this field as a measure, add the following advanced settings (by replacing NNNNN with actual field ID) and then importing this field via eazyBI import settings as dimension.

[jira.customfield_NNNNN]
data_type = "decimal"
measure = true

Jira calculated and scripted custom fields eazyBI for Jira

Single-issue picker and multi-issue picker field import

Create a new calculated field in Jira

Enable calculated custom field in eazyBI advanced settings

Import custom field into eazyBI

Examples

Environment

ScriptRunner for Jira

Jira Misc Custom Fields

eazyBI Advanced settings

Due date changes

ScriptRunner for Jira

Jira Misc Custom Fields

eazyBI Advanced settings

Numeric measure with multiple dates

ScriptRunner for Jira

Jira Misc Custom Fields

eazyBI Advanced settings

Original estimated hours history

ScriptRunner for Jira

Jira Misc Custom Fields

eazyBI Advanced settings

Original estimated hours change

ScriptRunner for Jira

eazyBI Advanced settings

Amount paid (ScriptRunner for Jira)

ScriptRunner for Jira

eazyBI Advanced settings

Jira calculated and scripted custom fields
eazyBI for Jira