I am new to EasyBI and need some tips on how to build a report that for a jira issue shows:
a) total working time (in hours) spent in Open status
b) total working time (in hours) spent in “In Progress” status
c) sum of a) + b)
a) and b) should consider loops among statuses.
By working time is considered Mon-Fri between 9 to 12 and 13 to 17 hrs.
Any hints will be greatly appreciated!
Welcome to the Community!
It gets a bit complicated when introducing specific hours to be considered, so I’ll first show you a simplified (but not so precise way how you could get the working time).
You can use Issue cycles to specify an “Open cycle”, which, when imported, will precalculate the “Open cycle workdays” measure that you can use it in reports. You can then create a new measure multiplying this “Open cycle workdays” measure by 8, to get a rough estimate of the working time in hours.
To get more granular and calculate the working hours by your specified time slots, you’ll need to define a new calculated custom field and use JS to calculate the time during the import.
To do this, go to the Source Data tab, Edit your Jira source and in the “Custom fields” section select the “Add new calculated field” option. You’ll need to create a separate calculated field for each status.
For example, for the “Open” status, give it an internal name like “openworkinghours” and display name “Open Working hours”. Choose the data type “decimal” and use the Custom fields assistant to help you create and adjust the JS code for the calculation.
Select it to be imported as a Measure and run the import.
This is the possible JS code that you can use for the “Open Working hours” measure that the assistant provided:
// Function to calculate working hours between two dates
// Working hours: Mon-Fri, 9:00-12:00 and 13:00-17:00 (6 hours per day)
function calculateWorkingHours(from, to) {
var startDate = new Date(Date.parse(from));
var endDate = new Date(Date.parse(to));
if (startDate >= endDate) return 0;
var totalHours = 0;
var currentDate = new Date(startDate);
while (currentDate < endDate) {
var dayOfWeek = currentDate.getDay();
// Skip weekends (0 = Sunday, 6 = Saturday)
if (dayOfWeek !== 0 && dayOfWeek !== 6) {
var dayStart = new Date(currentDate);
dayStart.setHours(9, 0, 0, 0);
var dayEnd = new Date(currentDate);
dayEnd.setHours(17, 0, 0, 0);
var effectiveStart = startDate > dayStart ? startDate : dayStart;
var effectiveEnd = endDate < dayEnd ? endDate : dayEnd;
if (effectiveStart < effectiveEnd) {
// Morning session: 9:00-12:00
var morningStart = new Date(currentDate);
morningStart.setHours(9, 0, 0, 0);
var morningEnd = new Date(currentDate);
morningEnd.setHours(12, 0, 0, 0);
var morningEffectiveStart = effectiveStart > morningStart ?
effectiveStart : morningStart;
var morningEffectiveEnd = effectiveEnd < morningEnd ?
effectiveEnd : morningEnd;
if (morningEffectiveStart < morningEffectiveEnd) {
totalHours +=
(morningEffectiveEnd - morningEffectiveStart) / (1000 * 60 * 60);
}
// Afternoon session: 13:00-17:00
var afternoonStart = new Date(currentDate);
afternoonStart.setHours(13, 0, 0, 0);
var afternoonEnd = new Date(currentDate);
afternoonEnd.setHours(17, 0, 0, 0);
var afternoonEffectiveStart = effectiveStart > afternoonStart ?
effectiveStart : afternoonStart;
var afternoonEffectiveEnd = effectiveEnd < afternoonEnd ?
effectiveEnd : afternoonEnd;
if (afternoonEffectiveStart < afternoonEffectiveEnd) {
totalHours +=
(afternoonEffectiveEnd - afternoonEffectiveStart) /
(1000 * 60 * 60);
}
}
}
// Move to next day
currentDate.setDate(currentDate.getDate() + 1);
currentDate.setHours(0, 0, 0, 0);
}
return totalHours;
}
// Calculate total working hours in "Open" status
var totalWorkingHours = 0;
var targetStatus = "Open";
var inTargetStatus = false;
var statusStartTime = null;
// Determine initial status from changelog
var initialStatus = null;
var hasStatusChanges = false;
if (issue.changelog && issue.changelog.histories) {
for (let i = 0; i < issue.changelog.histories.length; i++) {
let history = issue.changelog.histories[i];
for (let j = 0; j < history.items.length; j++) {
let historyItem = history.items[j];
if (historyItem.field === "status") {
initialStatus = historyItem.fromString;
hasStatusChanges = true;
break;
}
}
if (hasStatusChanges) break;
}
}
// If no status changes, initial status is current status
if (!hasStatusChanges) {
initialStatus = issue.fields.status.name;
}
// Check if issue was created in target status
if (initialStatus === targetStatus) {
inTargetStatus = true;
statusStartTime = issue.fields.created;
}
// Iterate through changelog to find all status transitions
if (issue.changelog && issue.changelog.histories) {
issue.changelog.histories.forEach(function(history) {
history.items.forEach(function(historyItem) {
if (historyItem.field === "status") {
var statusFrom = historyItem.fromString;
var statusTo = historyItem.toString;
var transitionTime = history.created;
// Entering target status
if (statusTo === targetStatus && statusFrom !== targetStatus) {
inTargetStatus = true;
statusStartTime = transitionTime;
}
// Leaving target status
else if (statusFrom === targetStatus && statusTo !== targetStatus) {
if (inTargetStatus && statusStartTime) {
totalWorkingHours +=
calculateWorkingHours(statusStartTime, transitionTime);
}
inTargetStatus = false;
statusStartTime = null;
}
}
});
});
}
// If issue is currently in target status, calculate up to now
if (inTargetStatus && statusStartTime) {
var now = new Date().toISOString();
totalWorkingHours += calculateWorkingHours(statusStartTime, now);
}
return totalWorkingHours;
Best regards,
Nauris