Active Sprints in Structure Formula

[Poojaa Sudhakaran]

Hello There,

I have a use case where I need to count the stories in open sprints in Structure. I tried using Structure Formula but I'm receiving an error when I'm trying to put "sprint in openSprint()".

My Use Case: 

a. Structure will list all the stories in the project

b. Stories are grouped by Teams

c. One column gives the Team wise count on active sprint, another column that shows team wise count on closed sprints

I cannot use Filter option in structure since I have to display both closed and active sprint in the same view.

Is there a generic way to mention active sprints in Structure Formula column instead of selecting each sprint?

Regards,

Poojaa

[Egor Tasa [ALM Works] · Accepted solution]

Hi Poojaa,

At the moment Structure Formula language cannot reference JQL functions and statements, though we have plans to eventually integrate this. Also, formulas cannot access sprint attributes like start, end dates, and status, except if they are made an issue's attributes. So potentially you can create a scriptable field that shows if the issue is in an open or closed sprint, and then formulas will be able to count issues based on their sprint status.

Sorry for the inconvenience.

Egor Tasa

ALM Works

[Poojaa Sudhakaran]

Thanks @Egor Tasa [ALM Works] 

I was able to achieve this after creating a script field and putting it in the formula.

Still it would be great if we can have jql support in Structure formula.

Regards,

Poojaa

[Egor Tasa [ALM Works]]

Yes, Poojaa, we have such plans.

Regards,
Egor

[Aasha]

@Poojaa Sudhakaran - Could you please help to achieve this in stucture?

One of my user requesting the same and unfortunately i couldnt?

[Poojaa Sudhakaran]

Hello @Aasha ,

There is a pre-requisite here. You need to create a scripted Field that'll display the status of your sprint in the issues page. I'm using Scriptrunner plugin's scripted field option to achieve this.

1. Once you have the field, please create a structure that'll display all the issues and group it by a field (In my case, I had to group the issues by Teams).

2. Add a Formula column in Structure and use the below formula for Active Sprint. You can remove the status conditions if you don't want them

IF(itemtype = "issue"; COUNT#all{Summary} and issueType = "Story" and sprintstatus = "Active" and (Status = "New" or Status = "In Progress" or Status = "Done"))

3. Use the below formula for Completed Sprints.

IF(itemtype = "issue"; COUNT#all{Summary} and issueType = "Story" and sprintstatus = "Closed" and (Status = "Done"))

Here, "sprintstatus" refers to my scripted field that'll display the issue's sprint status.

Regards,

Poojaa

[Aasha]

@Poojaa Sudhakaran  - Many thanks for explaining these much information. I havnt created scripted field. Could you please help me with that. Can you please guide me here. is there anyway you can share the script applied in the field. I am sorry i am a learner.

[Poojaa Sudhakaran]

Hello @Aasha ,

No problem. Please find below the code snippet for scripted field. Make sure to set your script field type as "Text".

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.greenhopper.service.sprint.Sprint

def sprintField = ComponentAccessor.customFieldManager.getCustomFieldObjectByName("Sprint")
ArrayList <Sprint> parentSprint = (ArrayList<Sprint>) issue.getCustomFieldValue(sprintField)
String result = null

for (Sprint sp in parentSprint) {
 if(sp.active) {
  result = "Active"
  break
 }
 if(sp.future) {
  result = "Future"
  break
 }
}

if(result == null) {
 for (Sprint sp in parentSprint) {
  if(sp.closed) {
   result = "Closed"
   break
  }
 }
}

return result

[Aasha]

@Poojaa Sudhakaran - I have created sprint status field as you mentioned but when i search this field this field is not showing values as Active, Future, Closed" in the jql query.

[Poojaa Sudhakaran]

Hello Aasha,

Make sure that the scripted field is added in the View screens of your issuetype and perform a reindex. Since this is a Text field, there will not be any dropdown values displayed. Please use something like below in JQL. 

"Sprint Status" ~ "Active"

It would be great if you can generate logs and send - it'll be easy to debug. 

Regards,

Poojaa

[Dave Rosenlund]

Hi @Poojaa Sudhakaran & @Aasha. There's some news that's relevant to this thread.

In case you missed it, ALM Works recently released Structure 7. The keystone feature is a major update to the Structure Formulas language, Expr.

Expr 2.0 includes major improvements that make it easier to extract project data from Jira and use it in creative ways in your reporting. If you want to learn more, this article will get you started. 

-dave [ALM Works]

[Aasha]

Thanks Dave

[Dave Rosenlund]

More news for you @Poojaa Sudhakaran & @Aasha:  ALM Works will be hosting a Structure 7 / Formulas / Expr 2 Bootcamp that you may be interested in.

Best,

-dave 

[Aasha]

@Poojaa Sudhakaran - Pooja surething. Can i share my screen and show you this it would be easy for you to take a look.

Please let me know your convenient time