Can we find out which issues were moved from one sprint to another?

Hello,

We would need to find out which issues were moved from one sprint to another.

Example:

Teams work in sprints that keep a naming convention: Spider Sprint 1, Spider Sprint 2 …, Spider Sprint 6.

We would need to know if there are any issues that moved from sprint 1 to sprint 2, from sprint 2 to sprint 3 and so on.

The measure ‘issues removed from sprint’ does not necessarily work for this because issues might be removed and put in the backlog.

Thank you!

I think an easier or better description of what we would need is:

At moment x, let’s say 1 MAR 2018, all issues were planned in certain sprints.

So in 1 MAR 2018, issues had certain sprints assigned to them.

We would want to know if we have the possibility to compare the sprint in which they were actually solved, with the Sprint in which they were planned.

When you move a ticket to a new sprint, do you also leave it in the previous sprint? If not, I’m not sure EBI would get the data in the import. If you do leave it in the old sprint, it then depends exactly what you want to report - do you just want to produce a list of tickets that were resolved beyond the sprint they were originally started in?

Hi,

Tickets are moved outside of the initial sprint in which they were planned.

So it would need a way to search in the history of the Sprint field, I assume.

The result would be: how many tickets were moved from one sprint to another / or for one ticket to show the planned sprint in a column (so the first sprint that issue ever had in its history), and the sprint it actually got solved in in another column.

I could be wrong, but I don’t think JIRA provides anything which is only shown in the Activity tab in the export. It has to be a piece of data which is currently populated in a field.

I think you’re right.

We came up with another idea. We added a label using the ‘Labels’ field, to all issues in all sprints for all teams that belong in the same Project, for the same Program Iteration.

So now I could check if the issue in a certain sprint had a certain label when the sprint started, or if an issue that had label ‘Sprint.1’ was actually solved in ‘Sprint.2’.

I am also in contact with the eazyBI team via email for this and I will update this post for more people to see an eventual solution.

Here is one general solution that can be used as a basis for several scenarios analyzing previous sprints of an issue. The suggested solution includes importing previous sprints as a property for an issue. Then you can use this information in several scenarios.

Here is a custom field definition with JavaScript code for custom field Previous sprints ID:

[jira.customfield_shistory]
name = "Previous sprints ID"
data_type = "string"
json_fields = ["customfield_NNNNN"]
javascript_code = '''
sprintHistory = new Array();
issue.changelog.histories.forEach(function(history){
  history.items.forEach(function(historyItem){
    if (historyItem.field == "Sprint") {
      sprintFrom = historyItem.from;
      if(sprintFrom){
        sprintHistory = sprintHistory.concat(sprintFrom.replace(/ /g, "").split(","));
      }
    }
  });
});
issue.fields.customfield_shistory = _.uniq(sprintHistory).join(",");
'''

Use Sprint custom field ID instead of NNNNN in the formula above.

Add the updated custom field definition to eazyBI advanced settings or ask Jira administrator to do this for you. eazyBI advanced settings are common for all accounts and only Jira administrators have access to them.

Open import settings for edit after changes in eazyBI advanced settings and select the custom field for import as a property and run an import. Import will create a new property Issue Previous Sprints ID in your account.

You can use this property after import in your reports and calculated measures. You can access Sprint members from this property with GetMembersByKeys function. For example, here is the formula for retrieving Previous Sprint names for an issue:

Case When
  Not isEmpty([Issue].CurrentHierarchyMember.Get('Previous sprints ID'))
Then
Generate(
  [Sprint].[Sprint].GetMembersByKeys(
    [Issue].CurrentHierarchyMember.Get('Previous sprints ID')),
  [Sprint].CurrentMember.Name, ", "
)
End

Property Issue Sprint gives you a current Sprint of an issue.

Daina / support@eazybi.com

2 Likes

Thank you very much, Daina!

We are happy to see our customers use the suggestions from this post to import ID’s of any previous sprint.

There is a more general JavaScript custom field example All Sprint ID. The custom field could be imported as property and will show you any sprint, including the current for any issue. You can use it in many custom calculations as well.

The custom field will include any sprint from the issue itself as well. You would like to update the code using Sprint custom field ID instead of NNNNN in the code below (2 places):

JavaScript custom field example for Server/DC
   [jira.customfield_allsprints]
    name = "All Sprint ID"
    data_type = "text"
    json_fields = ["customfield_NNNNN"]
    javascript_code = '''
    sprintsHistory = new Array();
    firstSprint = true;
    issue.changelog.histories.forEach(function(history){
      history.items.forEach(function(historyItem){
        if (historyItem.field == "Sprint") {
        	if (firstSprint) { 
        		sprintsFrom = historyItem.from;
        		if(sprintsFrom){
        			sprintsHistory = sprintsHistory.concat(sprintsFrom.replace(/ /g, "").split(","));
        		}
        		firstSprint = false;
        	}
    		sprintsTo = historyItem.to;
    		if(sprintsTo){
    			sprintsHistory = sprintsHistory.concat(sprintsTo.replace(/ /g, "").split(","));
    		}
        }
      });
    });

    //add all sprints from Sprint custom field as well:
    var sprintset = issue.fields.customfield_NNNNN;
    if (sprintset) {
      sprintset.forEach(function(sprintlong){
        var sprintidset = sprintlong.match("id=(\\d+)")
        if (sprintidset && sprintidset[1]) sprintsHistory.push(sprintidset[1]);
      });
    }

    issue.fields.customfield_allsprints = _.uniq(sprintsHistory).join(",");
    '''
JavaScript custom field example for Cloud
[jira.customfield_allsprints]
name = "All Sprint ID"
data_type = "text"
json_fields = ["customfield_NNNNN"]
javascript_code = '''
sprintsHistory = new Array();
firstSprint = true;
issue.changelog.histories.forEach(function(history){
  history.items.forEach(function(historyItem){
    if (historyItem.field == "Sprint") {
      if (firstSprint) {
        sprintsFrom = historyItem.from;
        if(sprintsFrom){
          sprintsHistory = sprintsHistory.concat(sprintsFrom.replace(/ /g, "").split(","));
        }
        firstSprint = false;
      }
    sprintsTo = historyItem.to;
    if(sprintsTo){
      sprintsHistory = sprintsHistory.concat(sprintsTo.replace(/ /g, "").split(","));
    }
    }
  });
});

//add all sprints from Sprint custom field as well. The change is in this part
var sprintset = issue.fields.customfield_NNNNN;
if (sprintset) {
  sprintset.forEach(function(sprintlong){
     sprintsHistory.push(String(sprintlong.id));
  });
}

issue.fields.customfield_allsprints = _.uniq(sprintsHistory).join(",");

'''

Add the custom field to eazyBI advanced settings and then select it for import in any account where you would like to define more complex scenarios on sprint data analysis.

Here is an example formula using this code to count issues moved to this sprint from the previous sprint:

CASE WHEN
    [Sprint].Currentmember.Level.name = "Sprint"
THEN
  NonZero(SUM(
    Filter(
      Descendants([Issue].Currentmember, [Issue].[Issue]),
      -- filter by properties
      -- issue is (was) in Sprint
      Cast([Sprint].CurrentMember.Key as String) MATCHES
        Replace(CoalesceEmpty([Measures].[Issue All Sprint ID], ""),",","|")
      AND
      -- issue was in previous Sprint as well
      Cast([Sprint].CurrentMember.PrevMember.Key as String) MATCHES
        Replace(CoalesceEmpty([Measures].[Issue All Sprint ID], ""),",","|")
     ),
   -- counter and a filter by report context
     [Measures].[Transitions to issues count]
  ))
END

This measure will show an issue for any sprint it was moved over, even it was moved to the next sprint several times.

Daina / support@eazybi.com

3 Likes

Hi Diana,

I did tried to perform this but getting an error.

Could you suggest.

Thank you!!

Manoj

You are using custom JavaScript code in import options. You would like to use JavaScript code only there (lines 6-17 including). You can do it for testing reasons (remove the code after testing) or when you need to implement account specific calculations (save import options with custom code if this is needed for account-specific calculations or data transformations).

While the formula works in import, the testing does not work for some time. We fixed access to sprint history items in custom javascript code with the version 6.1.

In this case, you would like to define a new custom field. You can define new custom fields in eazyBI advanced settings only. They are common for eazyBI instance. Once you define new calculated JavaScript custom field, you can aeelct it for import in any account.

Daina / support@eazybi.com

We’ve set up the javascript custom fields above. I wondering if there’s a way to identify the next sprint the issue rolled to. For instance, I’ve got several incomplete issues, some went to the backlog, others rolled to the next sprint and some where moved to a further out sprint. I want to create a calculated measure that will show where those issues ended up when the sprint was closed (Backlog (unsprinted), Next Sprint (if report was for 2021.6 and the issue moved to 2021.7) or if it was pushed further out to another future sprint (provide the sprint that it was moved to). Any ideas how I can accomplish this?

Hi @daina.tupule

Is there a way to get something like this to work for Fix Versions?

Cheers
Maree

eazyBI imports only versions currently added to issues. We do not import changes for Fix version. Therefore, there is no option to access previous versions if you removed them from the issue.

You can consider using some calculated fields in Jira and access all versions data there. Then you can import this information into eazyBI. While we do not have an example that is exactly for the cases, you can check this documentation page with some calculated field examples in Jira for inspiration.

Daina / support@eazybi.com

Hello Manoj,

You can use a default property Issue Sprints to see Jira field Sprint listed sprints. They will not a sprint if the issue was removed from a sprint before closing. We suggest using JavaScript calculated custom field All Sprint ID to import any sprint ID issue was a part of.

You can use the formulas below to seel a list of all sprints from All Sprint ID, rolled from, rolled to:

Issue all sprints

[Sprint].[Sprint].getMemberNamesByKeys(
  [Issue].CurrentHierarchyMember.get('All Sprint ID')
)

Rolled from

Generate(
  Head( 
    [Sprint].[Sprint].GetMembersByKeys(
      [Issue].CurrentMember.Get('All Sprint ID')
    ),
  Rank(
    [Sprint].CurrentMember,
    [Sprint].[Sprint].GetMembersByKeys(
      [Issue].CurrentMember.Get('All Sprint ID')
    )) -1 
),
[Sprint].CurrentMember.Name, ", "
)

Rolled to:

Generate(
  Tail( 
    [Sprint].[Sprint].GetMembersByKeys([Issue].CurrentMember.Get('All Sprint ID')),
    count([Sprint].[Sprint].GetMembersByKeys([Issue].CurrentMember.Get('All Sprint ID'))) 
    -
    Rank(
      [Sprint].CurrentMember,
      [Sprint].[Sprint].GetMembersByKeys([Issue].CurrentMember.Get('All Sprint ID'))
    )  
  ),
  [Sprint].CurrentMember.Name, ", "
)

Here is my report example with all those measures and the property Issue Sprint representing the final/last sprint of the issue. The issue last sprint (no sprint) represents that issue was completed outside of sprints.

Daina / support@eazybi.com

1 Like

Is it possible to just show sprints that had been started using the Sprint Issues measure?