Super new to EazyBi. Trying to generate a report that lists all JIRA case and notes down how long until the Assignee field was changed. Similar to tracking Transition Status, but for a group of Assignees instead.
Hope the question is clear enough.
Hi @kitbee
Welcome to the Community! 
You can define a new calculated measure in the Measures dimension that would use the DateDiffDays() function (you can also use DateDiffWorkdays to return only the number workdays) in the formula to return the number of days from “Issue created date” till the first time that the “Assignee” field had a transition.
With this formula, the number of days will only be calculated for those issues that originally had no “Assignee” assigned to them:
DateDiffDays(
[Issue].CurrentHierarchyMember.Get('Created at'),
TimestampToDate(
([Measures].[Transition from first timestamp],
[Transition Field].[Assignee],
[Assignee].[(unassigned)],
[Time].CurrentHierarchy.DefaultMember)
)
)
Note that you should set the formatting to Numeric → Decimal to display the number of days.
Select this measure for your report and drag the Issue dimension to the Rows section and in it, select the “Issue” level from the “All hierarchy level members” section.
You can read more on the Transition measures here in our documentation: Import issue change history.
​Let me know if this fits your use case or if you have any additional questions!
​Best regards,
​Nauris / eazyBI support
Thank you for this one! I tried it today, and it did not return results for many issues, as they usually already get automatically assigned to an Assignee from the moment of creation.
Ideally, we would like to track the ave. number of days it took for Assignee Group A to change the assignee to Assignee Group B.
ie. After the case was created, and assigned to Engineer 1. Engineer 1 assigned it to Engineer 2 for further input. We’d like to track how long Engineer 2 held the case, before assigning it back to Engineer 1.
This is because the status is more often not changed, and a comment is simply added and just assignee is changed after.
Is this possible?
Hi @kitbee
Thanks for the additional information!
In this case, you could define a new Javascript calculated field in the eazyBI Advanced settings which you can import as an additional measure in your report account. Then, use this measure with the Assignee dimension in your report to see how many days each Assignee spends on an issue.
See the example Javascript code on our documentation page: JavaScript calculated custom fields
When you’ve saved the Javascript code, head over to Source Data → Edit → Custom fields and select this “Days for assignee” custom field to be imported and trigger the import.
When the import finishes, go back to your report and define a new calculated measure in the Measures dimension with the following formula to calculate the average times:
Avg(
Filter(
DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Days for assignee] > 0
),
[Measures].[Days for assignee]
)
Use this measure in your report together with the Assignee and whichever other dimension you would like to see the data divided by:
Note that DescendantsSet is a rather resource-heavy function as it has to iterate through all of the issues that are imported in this report account to find the individual assignee times and calculate the averages. If this measure is performing slowly, please reach out to support@eazybi.com, and we’ll try to find ways to optimize the criteria specifically for your case!
​Let me know if this fits your use case or if you have any additional questions on this!
Best regards,
Nauris
Thank you very much! This made me realize I do not have permissions to access the advanced settings to add the new calculated field. I am working to see if I can get higher permissions or have someone add it in so I can run and check the report generated.
Hi @kitbee
If you don’t have access to Advanced settings, you can add a new calculated field instead.
Go to Source Data tab → Edit → Custom fields and select the “Add new calculated field”: New calculated fields
Fill out the fields in the following way:
Please use the following code in the “Custom JavaScript code” section:
var assigneehistory = new Array();
var datefrom = issue.fields.created;
var assignee = null;
resolution = false;
issue.changelog.histories.forEach(function(history){
history.items.forEach(function(historyItem){
if (historyItem.field == "assignee") {
assignee = historyItem.from;
if (! resolution) {
dateto = history.created;
if(assignee){
duration = (Date.parse(dateto) - Date.parse(datefrom)) / 1000 / 60 / 60 / 24;
assigneehistory.push(dateto.toString().substr(0,10) + "," + assignee + "," + duration);
}
datefrom = dateto;
assignee = historyItem.to;
}
}
if (historyItem.field == "resolution") {
if (historyItem.to) resolution = true;
else resolution = false;
}
});
});
if (issue.fields.resolutiondate && (issue.fields.assignee || assignee)) {
if (!assignee) assignee = issue.fields.assignee.name;
duration = (Date.parse(issue.fields.resolutiondate) - Date.parse(datefrom)) / 1000 / 60 / 60 / 24;
assigneehistory.push(issue.fields.resolutiondate.toString().substr(0,10) + "," + assignee + "," + duration);
}
return assigneehistory.join("\n");
Save the field, select it for import and start the import. Once the import finishes the “Days for Assignees” measure should be available in the Measures dimension.
​Let me know if this works as expected!
Best regards,
Nauris
Really appreciate the help. Unfortunately, I have very limited permissions and don’t have the tab either 
Currently waiting to see if I can get higher permissions to try the solutions suggested, but if there is any other workaround I can do, let me know
Hi @kitbee
Thanks for the info!
However, you will need to access the source options to implement this solution.
There are two ways how you can proceed:
- Ask the eazyBI account Owner or Jira admin to add you to this existing account as a “Data admin”. You can read more on user roles here: Account users
- Ask your Jira admin to create a new account for you and set you as the “Owner”/“Data admin” of this new account. You can then add the Jira data source and import projects that you have access to in your Jira.
Let me know if you have any updates on this or if you have additional questions!
Best regards,
Nauris
Hello @nauris.malitis ,
Sorry it took some time before the team was able to add this. They went and added this using Source Data tab → Edit → Custom fields with the code shared.
The output somehow does not average the days, and instead adds them up. Is there a way to get the average instead?
See Sample below:
Hi @kitbee
That’s great to hear that you’ve added the custom field!
Did you also define a new calculated measure in the Measures dimension and then use the formula that I previously posted to calculate the averages?
Avg(
Filter(
DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Days for Assignees] > 0
),
[Measures].[Days for Assignees]
)
Let me know if this formula works as expected or if you have any other questions on this!
Best regards,
Nauris
1 Like