If there are involved several workgroups to solve the ticket, standard imported Jira Service Management data are not enough. If you need to track each workgroup’s performance separately and measure the ticket status and assignment to a specific workgroup while excluding time when tickets are in the “waiting for customer“ status, you need to precalculated and import additional data into eazyBI.
The following is a technical solution with code snippets for the use case described in eazyBI blog.
Prepare data
Existing eazyBI default measures were not enough to calculate the time the workgroup was working on the issue until the issue reached a particular status as well as until the first comment made by the workgroup. As the workgroup information itself changed over time, the history of this field also should be imported additionally. All this could be done in advanced settings by defining a history change import and precalculating new metrics based on the information available in the issue changelog.
- Define the history import from Jira Service Management custom field “Workgroup”.
[jira.customfield_NNNNN]
data_type = "string"
dimension = true
multiple_values = false
separate_table = true
changes = true
- Precalculate “Days in Workgroup” measure using JavaScript calculated custom field based on the issue changelog. The JavaScript iterates through the issue history retrieve days the issues were assigned to a particular workgroup except for the time the issue was in the status “Waiting for cutsomer”. The workdays are calculated at the moment when the issue is assigned to another workgroup or when the issue is resolved. The JavaScript returns the result is in the following form:
Date1, workgroup A, Nr of days
Date2, workgroup B, Nr of days
Date3, workgroup A, Nr of days
Also, it returns the timestamp when the last workgroup transition happened to later calculate days for the issue current workgroup.
Using advanced settings, Days in workgroup value is imported as a measure with a decimal value and is mapped to the Time and Workgroup dimensions to analyze the measure in time. The timestamp is imported as an integer measure “Last workgroup timestamp”.
/*This measure works with the TIME dimension. It accumulates the active time of the ticket in a workgroup AFTER a transition from one workgroup to another. It doesn’t count days in the current workgroup, unless the issue is resolved.*/
[jira.customfield_wgr_t]
name = "Last workgroup timestamp"
data_type = "integer"
measure = true
[jira.customfield_dfwkgr]
name = "Days in Workgroup"
data_type = "decimal"
measure = true
multiple_dimensions = ["Time","Workgroup"]
split_by = ","
javascript_code = '''
var workgroupNotset = true;
var statusHistory = new Array(); // array of trasitions for results
var dateTo = null; // end date
var assignee = null; // workgroup
var new_wkgr = null; // next workgroup
var status = ""; // status
var excludeStatus = ["Waiting for customer"];
var resolution = false;
var startFrom = issue.fields.created;
var new_status = ""; // next status (status to)
var duration = 0; // how long issue been assigned to one assignee or status
// get first Wowkrgroup
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 == 'Workgroup' && workgroupNotset){ // current assighee if no history of assignee change
if(item.fromString) {
wkgr = item.fromString;
} else {
wkgr = "(no workgroup)";
}
workgroupNotset = false;
break;
}
}
}
}
}
// code to add [...] to the workgroup name that is imported from Jira
if (workgroupNotset) {
if(issue.fields.customfield_10305) {
wkgr = "[" + issue.fields.customfield_10305 + "]"
}
else wkgr = "(no workgroup)"
}
// main algorithm to calculate time between transitions
issue.changelog.histories.forEach(function(history){
history.items.forEach(function(historyItem){
if (historyItem.field == "Workgroup" || historyItem.field == "status" || historyItem.field == "resolution"){
dateTo = history.created;
if (historyItem.field == "Workgroup"){ // Workgroup change
if(historyItem.fromString != null){ // actual Workgroup
wkgr = historyItem.fromString;
} else {
wkgr = "(no workgroup)";
}
if(historyItem.toString != null){ // set next workgroup
new_wkgr = historyItem.toString;
} else {
new_wkgr = "(no workgroup)";
}
if(excludeStatus.indexOf(status) == -1 && ! resolution) {
duration = (Date.parse(dateTo) - Date.parse(startFrom)) / 1000 / 60 / 60 / 24;
statusHistory.push(dateTo.toString().substr(0,10) + "," + wkgr + "," + duration);
}
startFrom = dateTo;
wkgr = new_wkgr;
}
if (historyItem.field == "resolution") {
if(historyItem.to){
if(historyItem.from == null && excludeStatus.indexOf(status) == -1){
duration = (Date.parse(dateTo) - Date.parse(startFrom)) / 1000 / 60 / 60 / 24;
statusHistory.push(dateTo.toString().substr(0,10) + "," + wkgr + "," + duration);
}
resolution = true;
}
else resolution = false;
}
if (historyItem.field == "status"){ // status change
status = historyItem.fromString; // moves from statuss
new_status = historyItem.toString; // to next status
if(excludeStatus.indexOf(new_status) > -1 && ! resolution) {
duration = (Date.parse(dateTo) - Date.parse(startFrom)) / 1000 / 60 / 60 / 24;
statusHistory.push(dateTo.toString().substr(0,10) + "," + wkgr + "," + duration);
}
else if(excludeStatus.indexOf(status) > -1 && excludeStatus.indexOf(new_status) == -1 && ! resolution) {
startFrom = dateTo;
}
status = new_status;
}
}
});
});
issue.fields.customfield_dfwkgr = statusHistory.join("\n");
issue.fields.customfield_wgr_t = Date.parse(startFrom)/1000
'''
Create new calculated Measures
After importing additional measures, they were combined with existing eazyBI measures for further calculations. Add new calculated measures in Define new calculated measure, to allow queries of the eazyBI cube with and without the time dimension.
There are some of the measure examples that can be created based on eazyBI standard measures as well the defined custom calculations.
- Cumulated active days for the SLA: calculates total days spent within the selected workgroup. This is measure can not be used with the Time dimension.
--For tickets in previous and current workgroup (does not work with time dimension)
SUM(
Filter(
Descendants([issue].CurrentHierarchyMember,[Issue].[Issue]),
(
[Measures].[Open issues],
[Workgroup].DefaultMember
)>0
OR
(
[Measures].[Issues resolved],
[Workgroup].DefaultMember
)>0
),
Cache(
Sum(
{PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
[Measures].[Days in workgroup] --measure imported from javascript custom field
)
)
)
+
--for open issues in good status time since they went to good status till the end of month or till today for current month
Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- filter open issues in period using issue properties Created date and Resolution date only
IsEmpty([Issue].CurrentMember.get('Resolved at'))
AND
[Measures].[Issue status]<> "Waiting for customer" --to exclude bad status doublcheck the name of status here
)
,
CASE WHEN
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember) --for current month
THEN DateDiffDays(
timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
Now()
)
ELSE DateDiffDays( --for historical months
timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
[Time].CurrentHierarchyMember.NextStartDate
)
END
END
)
- Cumulative active days open issues - Time. Calculates total days spent within the workgroup in each selected time period.
--cumulative days in workgroup, works only with time dimension
CASE WHEN
DateafterPeriodEnd(
'today',
[Time].CurrentHierarchyMember.PrevMember)
THEN
SUM(
Filter(
Descendants([issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Open issues]>0
OR
[Measures].[Issues resolved]>0
),
Cache(
Sum({PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
[Measures].[Days in Workgroup] --measure imported from javascript custom field
)
)
)
END
+
--for open issues in good status time since they went to good status till the end of month or till today for current month
CASE WHEN
[Measures].[Open issues] > 0
AND
DateafterPeriodEnd(
'today',
[Time].CurrentHierarchyMember.PrevMember)
THEN
Sum(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- filter open issues in period using issue properties Created date and Resolution date only
IsEmpty([Issue].CurrentMember.get('Resolved at'))
AND
--not to show anything that is covered already by Javascript
DateBeforePeriodEnd(
timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
[Time].CurrentHierarchyMember)
AND
[Measures].[Issue status]<> "Waiting for customer" --to exclude bad status doublcheck the name of status here
)
,
CASE WHEN
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember) --for current month
THEN
DateDiffDays(
timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
Now())
ELSE DateDiffDays( --for historical months
timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
[Time].CurrentHierarchyMember.NextStartDate)
END
END
)
END
- Time in current Workgroup (counted from the last workgroup change).
DateDiffDays(
TimestampToDate(
[Measures].[Last workgroup timestamp due] /
[Measures].[Issues due]
),
Now()
)*
[Measures].[Issues due]
Create reports
eazyBI dimensions were used together with all those calculated measures as well as predefined ones to create a set of SLA reporting. Some of the final report examples are described in the blog post.