We do not log time to issues in Jira. So I would like to get a list of all issues that were set to " In Progress" during a certain sprint(need not be necessarily closed ) and how long they were in that status during the sprint so I can get an idea of which issues we spent time on during a that sprint.
Is there any easy way to do this ?
Please note that I am not looking at issues resolved during the sprint , rather where we spent time irrespective of the outcome on those issues.
Hi @Ukesh_Upendran,
If you are looking to track time, most probably you are also looking for the Assignee and the Time dimensions in addition to Sprint.
Although it might be possible to identify the issues transiting to a specific status during a sprint by MDX calculations, that would not allow calculating the time spent in that status efficiently.
The transitions to and from specific statuses are registered in the issue changelog in Jira and that data could be used to calculate the time spent in specific statuses for the Sprint+Assignee combinations.
The MDX calculation would become very slow as the expression would need to iterate through each issue and each historical change to keep track of the situation.
You might better define an additional JavaScript-calculated custom field to do these calculations during the data import. Please read about creating JavaScript-calculated custom fields here - JavaScript calculated custom fields.
The code for the specific need might be as follows.
[jira.customfield_spentTime]
name="Hours in progress by assignee and sprint"
data_type="decimal"
measure=true
multiple_dimensions=["Time","Assignee","Sprint"]
javascript_code='''
var isWorked = false;
var forCount = false;
var forRecord = false;
var duration = 0;
var currAssignee = "(unassigned)";
var oldAssignee="(unassigned)";
var currSprint = -1;
var oldSprint = -1;
var currStartDate = null;
var oldStartDate = null;
var result = new Array();
var workStatuses = ["In Progress"]
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") {
if (workStatuses.indexOf(item.toString) > -1) {
isWorked = true;
forCount = true;
forRecord = false;
} else if (workStatuses.indexOf(item.toString) == -1 && isWorked) {
isWorked = false;
forCount = true;
}
}
else if (item.field == "Sprint") {
if (oldSprint == -1) {
currSprint = item.to;
forRecord = true;
} else if (oldSprint !== -1) {
var sprints = item.to;
oldSprint = currSprint;
currSprint = sprints.substr(currSprint.lastIndexOf(",")+1, currSprint.length);
forRecord = true;
}
}
else if (item.field == "assignee") {
oldAssignee = currAssignee;
currAssignee = item.to;
if (!currAssignee) {currAssignee = "(unassigned)";}
forRecord = true;
}
if (isWorked && forCount) {
currStartDate = history.created;
forCount = false;
}
if ((isWorked && forRecord)||(!isWorked && forCount) ) {
oldStartDate = currStartDate;
currStartDate = history.created;
duration = ((Date.parse(currStartDate) - Date.parse(oldStartDate)) / 1000 / 60 / 60);
result.push( currStartDate.substr(0,10)+ "," + oldAssignee + "," +oldSprint+","+ duration);
oldAssignee = currAssignee;
oldSprint = currSprint;
forRecord = false;
forCount = false;
}
else if (!isWorked && forRecord) {
oldStartDate = currStartDate;
currStartDate = history.created;
oldAssignee = currAssignee;
oldSprint = currSprint;
forRecord = false;
}
}
}
}
if(result){
issue.fields.customfield_spentTime = result.join("\n");
}
}
'''
After importing this custom field as a measure, you can track the time the issues spent in selected work statuses per Assignee and Sprint.
Regards,
Oskars / support@eazyBI.com
1 Like