Excel plugin exports multiple sprint values in the sprint field.

[Deleted user]

Hi Lovely JIRA community.

In our Company we use excel reports to work with planning of future sprints based on remaining work estimate. In this process we are very fond users of the Jira Excel plugin that imports data directly from Jira.

We have hit a snag that is annoying our PM's a lot is the fact that the jira table imported for sprints some times contain multiple sprint values (e.g. Sprint21;Sprint19;Sprint3). In order to use sprint in a Pivot table this data needs to be manually changed and only reflect the latest sprint. 

This occurs if a tasks was in a sprint that was completed and the task moved to next sprint or when a task is reopened and added to a sprint.

Our current workaround we would like to avoid in excel:

1. Filter on column on ;
2. Manually delete the old values for each line with more than one sprint
3. Remove filter

I hope someone knows a nice workaround or notice what we are doing wrong.

[Bill Sheboy · Accepted solution]

Hi @[deleted] 

As Nic notes, this behavior appears to be by-design in Jira.  A few things you could try to help mitigate this behavior are:

• Try not to re-open a task completed in a prior sprint.  When an issue needs re-work, treat that as a brand new work item, and link the new issue to the original for visibility/tracking.
• If your sprints have a consistent naming convention, create an additional column in the spreadsheet to parse out what you believe to be the latest sprint using a formula
• If your sprints do not have a consistent naming convention, you could track the most-current sprint an issue is in by:
  • Add a custom field to contain the latest sprint
  • Use an automation rule (or JQL marketplace addon) to set that custom field when an issue starts in a sprint
  • Add that custom field to your export/data pull for reporting

Kind regards,
Bill

[Deleted user]

Hi @Bill Sheboy 

Thank you Bill for the suggested solution.

Our sprints are rather consistent 2101 for week one of 2021 and 2102 for week two and so on. I will have to chat with some Excel pros for a solution for the formula. I think this is the most realistic solution for us. 

I really appreciate the quick response. Thank you.

Regards

Henrik

[Ram Kumar Subramaniam]

Hi @[deleted] , I have found a solution for this using the "Jira Cloud for Sheets" plugin in google sheets. I am sure it should be the same the plugin for excel

[Nic Brough -Adaptavist- · Accepted solution]

I'm afraid this is by design, and there's nothing you can do about it in the export process.

Sprint is a field that contains the list of all sprints the issue has been a part of.  In Jira, there is code that works out "latest sprint" and "active sprint(s)" from that, and the sprint data, when they're needed, but it's not available as a field.

So the exporter has nothing to to get a hold of, and when you tell it to export the sprint, it simply gives you the content of the field, which is the list of all sprints for the issue.

You have two options here - write something that works out the sprint you're interested in for any given issue, and makes it available for the exporter to get hold of, or manipulate the data after you've exported (which is exactly what you are already doing)

[Deleted user]

Hi @Nic Brough -Adaptavist- 

Thanks, this presented the limitations very well. Data manipulation is a step too deep in Jira for me at the moment, but I do understand your solution description.

Thank you and keep up the good work.

Regards

Henrik

[Aron Gombas _Midori_]

With the Better Excel Exporter app, you can customize how each field value from any field type is exported!

A. This is technically possible to split the "sprint1, sprint2, sprint3" field value at comma, the load each of the referenced sprints, search for the "last" one (whatever it means) and export only that to the "Sprint" column in Excel.

B. Or, without changing the original logic behind "Sprint", you can introduce a new calculated field "Last Sprint", then export both the original and the calculated field!

[Deleted user]

Thanks Aron.

It have a suitable solution to my problem now. it was solved by some Russian Excel god on the excel community. 

Somehow this solves it in excel:

= HVIS( ER.TAL(SØG("sprint week";G2));
"Sprint week " &
MAKS(
FILTRERXML( "<t><s>" &
UDSKIFT(
UDSKIFT(
UDSKIFT(SMÅ.BOGSTAVER(G2); "sprint week "; "");
";"; "|"
); "|"; "</s><s>"
) & "</s></t>";
"//s") );
"")

[Aron Gombas _Midori_]

I agree, it should be part of Greek Mythology. :-D

[Deleted user]

@[deleted] Can you post that as function you wrote in English?

And am I correct in my understanding that that function/macro works when Excel has opened an XML-format exported field?

[Deleted user]

Hi,

This is the link to the english version I received. 

https://techcommunity.microsoft.com/t5/excel/selecting-sprint-with-highest-number-from-a-string-in-a-cell/m-p/2823635#M117112

[Deleted user]

Thanks for that...it looks like it performs its search from an XML dump.

I guess I'll have to research how to perform the same thing if you don't.

I'm working with a simple XLS, saved from a CSV, initially.

Appreciate the link, though.

[Ram Kumar Subramaniam]

Get ready to say "Bye bye" Russian Excel god :)

I used a Sprint.name field to resolve this. It always contains the latest Sprint that the ticket was in.

Hope this works for you too

[Deleted user]

Now we are just having fun finding the best solution :) 

Why don't i have Sprint.name in Jira cloud?

[Ram Kumar Subramaniam]

Note : I have not tried it in Excel, however I think it should be the same as in Google sheets.

Expand the dropdown highlighted against the "Sprint" field and you will be able to see the "name" attribute of "Sprint" field. PS : Not shown in snapshot below.