Data adjustments using JavaScript

eazyBI uses JavaScript code to extend import options for some data sources. Use JavaScript to calculate some values that could be imported into eazyBI as new measures, dimensions, or properties. JavaScript could be used to change data of some data sources as well. 

This page describes advanced eazyBI usage. If you need help with this please contact eazyBI support.

Underscore.js library usage

eazyBI uses basic JavaScript with https://underscorejs.org/ library. 

The example below uses _.map to map one source data row to multiple data rows in Custom JavaScript code for REST API data import. For example, if doc is an invoice object with several lines, then you can return an array of invoice lines which contains both invoice and line attributes:

return _.map(doc.lines, function(line) {
  return {
    invoice_number: doc.number,
    invoice_customer: doc.customer,
    invoice_date: doc.date,
    line_product: line.product,
    line_item_count: line.item_count,
    line_item_amount: line.item_amount
  };
});

Functions for dates

REST API retrieves dates as strings. In many cases, they should be converted to objects with a type date to access date object methods.

Function Date.parse(<string expression>) converts any date as a string in ISO8601 format to a timestamp (UTC). JavaScript can use timestamp to generate objects with a type date.

The function strftime (<string expression>, <date_object>)  will convert an object with a type date to a string in a representation defined by string expression.

The example below is used in calculated JavaScript custom field in Jira data import and will use Date.parse and new Date to convert an issue created date to an object with a type date. We used function strftime to generate a string of Year and Month from this converted creation date:

[jira.customfield_month_of_year]
name = "Created Month and Year"
data_type = "string"
dimension = true
javascript_code = '''
  issue.fields.customfield_month_of_year = strftime("%Y %B", new Date(Date.parse(issue.fields.created)));
'''

Get additional data from REST APIs

eazyBI allows calling another REST API (on the same BASE URL) to access more details of any object using functions getDocument , getCachedDocument and postDocument.

Functions getDocument, getCachedDocument

Use REST API URL for GET requests, add which error codes to ignore for GET calls if necessary:

getDocument(<URL>, {ignoreErrors: [<list of error codes>]}) 

getCachedDocument(<URL>, {ignoreErrors: [<list of error codes>]})  

getCachedDocument works identically to getDocument, but stores the received result in the cache. The use of getCachedDocument is more efficient if many similar requests are expected during the data import runtime.

The example below uses additional data import with REST API  to import additional details for sprints within each board in Jira issue cube. Define a new REST API source data to access a list of boards. Use Custom JavaScript code with function getDocument to access all sprints for those boards.

The example below creates a list of boards and sprints within this board. It will give a number from 1 to N in descending order for each closed sprint within a board representing last closed sprints.

var sprints = [];
if (doc.type == "scrum" ) { 
getDocument( "/rest/agile/1.0/board/" + doc.id + "/sprint", {ignoreErrors: [404]}, function(result){
  if (result && result.values ) { 
    j = 1;
    for(var i = result.values.length-1; i>= 0; i--) {
      doc.i = i;
      var sprint = result.values[i];
      if (sprint && sprint.state.equals("closed")) {
        sprints.push({
          board_id: doc.id,
          sprint_id: sprint.id,
          sprint_last_closed: j
        })
        j ++;
     }
    }
  } 
  });
}
return sprints;

The example above uses Atlassian REST API to access Sprints of a board in the example above.

Function postDocument

Use POST REST API calls with specified post request to designated URL with JSON content type:

postDocument(<URL>, <POST_REQUEST>, {content_type: 'application/json'})

The example below uses additional data import with REST API  to import the hottest color (Red, Yellow, or Green) from any open sub-tasks for a parent issue. Define a REST API source and retrieve parent issues using Get REST API request. Use Custom JavaScript code with function postDocument to access all sub-tasks of each parent issue with custom field color to calculate the hottest color from all sub-tasks. 

if (doc.fields.subtasks ) {
// create a post request body
    body = {
    jql: "Color is not EMPTY AND resolution is not EMPTY AND parent = " + doc.key,
     fields: [
         "customfield_11303" // custom field Color in sub-tasks
     ]
    }

// call post request 
  response = postDocument("/rest/api/2/search", body, {content_type: 'application/json'});
// there is a response with sub-tasks. get color from any sub-tasks and set the hottest color for the parent issue
   if (response && response.issues) {
     Red = false;
     Green = false;
     Yellow = false;
     for (var i = 0; i < response.issues.length; i++) {     
       subtaskColor = response.issues[i].fields.customfield_11303.value;
       switch (subtaskColor) {
         case "Red":
           Red = true;
           break;
         case "Yellow":
           Yellow = true;
           break;
         case "Green":
           Green = true
           break;
       }  
     }
// set the color for parent issue
    if (Red) {
      doc.color = "Red"
    } else if (Yellow) {
      doc.color = "Yellow"
    } else if (Green) {
       doc.color = "Green"
    }
  }
}

The example above uses Atlassian search using search request REST API.

Common JavaScript code for Jira data import

eazyBI allows to define some functions you can use within any JavaScript code for Jira data import (in Custom JavaScript code in Jira import options and in JavaScript calculated custom fields). Add [jira] to eazyBI advanced settings for custom fields and then add any function you would like to use in the parameter comon_javascript_code.

[jira]
common_javascript_code = '''
// add your functions here
'''

Then you can use those functions within JavaScript for Jira data import in JavaScript calculated custom fields and Custom JavaScript code.

See examples of two functions setNumber and workdays you can add in common JavaScript code. 

Change data type to Number

Some data type conversations might lead to import errors when not treated correctly. The function below converts string values to numeric values and will retrieve empty result if there will be any problems converting it:

function setNumber(str)
{
  num = Number(str);
  if ( Object.prototype.toString.call(num) === "[object Number]" && !isNaN( num )) {
    return num;
  }
  else {
    return null;
  }
}

The function could be used with custom fields if number value is added as a text or as select list in Jira.

The example below uses the common JavaScript function setNumber and works for Jira custom field select list (single select) with numeric integer values selection. The example includes custom field definition for the field and JavaScript calculation:

[jira.customfield_NNNNN]
data_type = "integer"
measure = true
javascript_code = '''
if(issue.fields.customfield_NNNNN && issue.fields.customfield_NNNNN.value) {
str = issue.fields.customfield_NNNNN.value;
issue.fields.customfield_NNNNN = setNumber(str);
}
'''

Use custom field ID instead of NNNNN in the definition above. Updated custom field definition could be used in eazyBI advanced settings if function setNumbers is defined as common javascript function. 

Workdays

eazyBI supports only some default measures based on workdays. The function workdays will calculate workdays from two dates:

function workdays ( from, to ) {
  var c1 = new Date(Date.parse(from));
  var c2 = new Date(Date.parse(to));  
  var days = 0.0;
  var fullWeeks = 0;
  var nonworkCount = 0;
  nonworkdays = [0,6]; // non-working days Saturday and Sunday
  
  // check that from date is before to date
  if (c1 > c2) return -workdays(to, from );
  // move from date to first workday  
  while (true) {
  w1 = c1.getDay();
  if (nonworkdays.indexOf(w1) > -1 ) {
    c1.setHours(0);
    c1.setMinutes(0);
    c1.setSeconds(0);
    c1.setMilliseconds(0);
    c1.setDate( c1.getDate() + 1 );
  }else{break;}
}
  // move to date to first workday
   while (true) {
    w2 = c2.getDay();
    if (nonworkdays.indexOf(w2) > -1 ) {        
      c2.setHours(0);
      c2.setMinutes(0);
      c2.setSeconds(0);
      c2.setMilliseconds(0);
    c2.setDate( c2.getDate() + 1 );;
    } else{break;}
   }
   days = (c2.getTime() - c1.getTime()) / 86400000.0;
   fullWeeks =  Math.floor(days / 7); 
   nonworkCount = fullWeeks * nonworkdays.length;  
  //count non-workdays in last week    
  c3 = new Date(c1.getTime() + fullWeeks * 7 * 86400000);
  while (c3 < c2) {
     w3 = c3.getDay();
     if (nonworkdays.indexOf(w3) > -1)  {
      nonworkCount += 1;
    }
      c3.setDate( c3.getDate() + 1 );
  }
  return days - nonworkCount;
}

The example below uses function workdays to calculate total development cycle in workdays for an issue:

[jira.customfield_devwdays]
name = "Development workdays"
data_type = "decimal"
measure = true
javascript_code = '''
var transitto = issue.fields.created;
// calculate development cycle for resolved issues only
if ( issue.fields.resolutiondate ) {
var devStatusList = ["Development","In Progress"]; //list of development statuses
var devWorkDays = null;
var transitfrom = null;
issue.changelog.histories.forEach(function(history){
  history.items.forEach(function(historyItem){
    if (historyItem.field == "status") {
      transitfrom = history.created;
      status = historyItem.fromString;
      // add workdays in a status when an issue moves out of any development status
      if (devStatusList.indexOf(status) > -1) {
         devWorkDays +=  workdays (transitto , transitfrom ) ; 
      }
      transitto = transitfrom; 
    }
  });
});

issue.fields.customfield_devwdays = devWorkDays;
}
'''

Custom field definition could be used in eazyBI advanced settings if function workdays is defined as common javascript function. 

Debugging JavaScript

eazyBI has two functions console.log() and console.error() for JavaScript debugging. Those functions will print additional information on the screen while you are testing JavaScript in custom JavaScript code or save them in log files if you will use them in JavaScript for data import.

console.log("issue " + issue.key + " created date: " + issue.fields.created);
if (issue.fields.resolution) {
  if (issue.fields.resolutiondate) {
    console.log("issue " + issue.key + " resolution date:" + issue.fields.resolutiondate)
  } else 
  { console.error("resolution date missing for resolved issue: " + issue.key) }
}

The example below works in custom JavaScript code in Jira import options for testing of one issue. It prints out the issue creation date for this issue. Then it checks if an issue is resolved and will show either resolution date or show an error if the resolution date is missing. In this case, it shows error for missing resolution date:

Functions console.log() and console.error() in JavaScript code during import will print out results in the queue log:

See also:

• JavaScript calculated custom fields
• Custom JavaScript code
• Import from REST API
• Zendesk custom JavaScript code