What is the easiest way to automatically sum up story points from child issues to the parent epic?

[john_howard]

Ideally I'd like to have Epics present totals for:

Todo Points:  ( Summed story points from all issues, for this epic, whose status is not In QA nor Done )

Total Points:  ( Summed story points from all issues for this epic )

Percent complete:  ( ( 1 - ( Todo Points / Total Points ) ) * 100 ) 

Just some basic summations and calculations; which are easily done in SQL or with databases, yet I have not found any straight forward way to do this in Jira ...

[john_howard · Accepted solution]

Note -- I've found that setting the Epic points to a value using:
     {{#=}} 2 {{/}}
does work fine.  
However, setting the Epic points to a value using:
   {{lookupissues.Story Points.sum}}
or
  {{#=}}  {{lookupissues.Story Points.sum}}  {{/}}
does not even though the Audit Log clearly indicates that child issues ( lookupissues ) were found and that they do have points ...

[Bill Sheboy]

Automation rule smart values are often case-sensitive, so please try this for the lookup:

{{lookupIssues.Story Points.sum}}

Also, try adding some information to the audit log (or add an email send) to dump out the looked up issues:

Count: {{lookupIssues.size}}

The list: {{#lookupIssues}}{{key}}: {{Story Points}}{{^last}}; {{/}}{{/}}

[john_howard]

Re. "Automation rule smart values are often case-sensitive" --
THANKS @Bill Sheboy !! Yes, that was it -- the small "i" needed to be a capital "I" in lookupIssues    -- I was staring at this too long

I'm getting reasonable results now:

However, 4/18 should be 22% so I'm not sure what's going on with this math:
{{issue.Actual Story Points.divide(issue.Story Points).asPercentage}}

Thanks

[Bill Sheboy]

John, I am glad to hear that worked for you.  

If that math results seem unexpected, consider logging the values to see what is up: numerator and denominator.  I believe asPercentage rounds (not truncates) so the input values may be the factor.

[john_howard · Accepted solution]

Thanks for the follow up. 
Re. " "Epic Link" = {{issue.key}} " --- this is under the "For Parent" branch thus this is looking up issues where the "Epic Link" of the child issue matches that of the parent issue.key. That is the "issue.key" is the key of the Epic. And this does seem to be working since all of the appropriate child issues are found per the Audit Log.

This is a Classic project on Atlassian cloud. Something also odd that I've noticed is that although the Audit Log shows that the automation ran today, and found the right items, and edited the parent Epic the History for the parent Epic shows only that the automation rule made edits to that Epic yesterday. Is there some limitation to Automation that it can only update an item a certain number of times or something like that? 

[Bill Sheboy]

Oops, sorry that I missed your nesting for the epic link check.  You could have used {{triggerIssue.epic link}} instead to be what I intended.

No limits on edits, only limits on executions for global rules and for a "run-away", where the rule execution get halted due to rapid execution looping.

[Bill Sheboy · Accepted solution]

Hi @john_howard  -- Welcome to the Atlassian Community!

You could do this by adding custom fields to your epic and updating them with a scheduled automation rule.  Maybe something like this:

• Trigger: scheduled with JQL to check just the epics that are not done/completed
• Action: lookup issues with JQL for issues in the epic.

"epic link" = {{issue.key}}

• Action: create variable (varTotalPoints) to sum up the story point total. 

{{lookupIssues.Story Points.sum}}

• Action: lookup issues with JQL for open issues in the epic. 

"epic link" = {{issue.key}} AND status NOT IN (QA, Done)

• Action: create variable (varTodoPoints) to sum up the story points ToDo. 

{{lookupIssues.Story Points.sum}}

• Action: edit the epic to set the values, including your percent complete

{{#=}}(1 - ( {{varToDoPoints}} / {{varTotalPoints}} ) ) * 100{{/}}

Please see these sources for documentation and some example rules:

https://community.atlassian.com/t5/Automation-articles/Jira-Automation-all-the-resources-you-need-in-one-page/ba-p/1388465

https://www.atlassian.com/software/jira/automation-template-library#/label/all/1453

Best regards,

Bill

[john_howard]

Thanks @Bill Sheboy . Below is the Automation Rule that I added. The rule runs, and the Audit log shows that it does find the child records, and it does update straight text, however, the calculations do not seem to get the "lookupissues" values nor update the parent Epic:

[john_howard]

And even though the Audit log shows that a bunch of Lookup issues were found, and I even added a clause to make sure that they all had Story Points ( e.g. AND Story Points is not Empty ) the end result of the update on the parent for Story Points = {{lookupissues.Story Points.sum}}
is:

That's right, for some reason Jira thinks {{lookupissues.Story Points.sum}} = "None" 

Any idea why {{lookupissues.Story Points.sum}} would evaluate to "None"?

[Bill Sheboy]

Hi John,

I noted that you changed the trigger to be from the story level (after a story point change) rather than scheduled. 

That probably will work, however please note in your lookup issues JQL, you are testing "Epic Link" = {{issue.key}}  That would try to match a story's key as an epic, which cannot work.  Instead you want "Epic Link" = {{issue.epic link}} AND...

This may also be why the story points got cleared to "None", because the Lookup Issues is empty (null).  When expecting a number to possibly be empty, you may use the default handling like this to replace with zero: {{issue.Story Points|0}

And one more thing to confirm, this is for a Classic project, correct?  If it is for NextGen, then use "Story Point Estimate" rather than "Story Points".

[Marcel Rossouw]

Hi Bill, this has been very helpful.
Is there a way to tally up the total story point estimates achieved within an active sprint based on the change made in real time? *team managed project and our sprint and story point estimate fields are custom fields hence the CF id's

Context:
If a total story point estimate is reached that is over target, I have managed to create an automation rule that alerts me to what tickets have triggered this greater than or equal to (gte) story point estimate within an active sprint. What I don't have is that total story point estimate value now which is greater than the X value set, is there a way to add this to the email alert?

This is what I currently have that works

[Bill Sheboy]

Hi @Marcel Rossouw 

First thing, I recommend discussing this behavior with the team and product owner.  Changing the sprint scope (or the sizing for any issues currently in the sprint) is a concern for the entire scrum team.

From what you describe, you would need either one or two rules to detect changes to either the Sprint field or the Story point estimate field.

The current sum of Story point estimate values could be obtained using a Lookup Issues action with this JQL: project = yourProjectName and sprint IN openSprints()

With the sum from that lookup being {{lookupIssues.Story point estimate.sum|0}}

Your list iteration in that email body is incorrect.  When you want to list the issues in the current sprint, that would be this:

{{#lookupIssues}}
* <a href="{{url}}">{{key}}</a> - {{summary}}
{{/}

Note the issue prefix is not used when inside of a Lookup Issues iterator.

Kind regards,
Bill

[Marcel Rossouw]

Bill, thanks a lot. I agree about the behaviour of sprint scope hence this alert. We find that our team is increasing in scope (our work requires frequent changes, tickets to be reprioritised) whilst the team is not always swapping out tickets or deprioritising for new tickets. The goal here is to alert the assignee, team leaders of this and start driving this behaviour down by automation alert.

What would be even more ideal (if at all possible) is for automation to monitor each new sprint that commenced monitoring it's total story point estimates at time of start as this varies for each new sprint.

[Bill Sheboy]

I believe what you describe is already in the sprint report.

And...this has the smell of opportunities to improve backlog refinement, sprint planning, and / or team focus after starting the sprint.  Sounds like valuable retrospective topics.

Perhaps a cheap, and temporary, way to do this is with card colors on the Jira board:

• add an automation rule which adds a label to an issue if it is added after the sprint started
• add an automation rule which adds a different label if the story points are changed to an issue already in the current sprint
• add filters for card colors to the board, detecting the labels
• at the daily stand-up, note and discuss any cards with the color indicators
• as the symptom decreases and then stops, the rules can be disabled and colors removed

[Marcel Rossouw]

Excellent, thanks. Yes the Sprint reports have this information however would like more granular information that is accessible in other ways but thank you for the ideas - they sound like a plan!

[Marcel Rossouw]

Hmm seems I suggested an answer but was trying to reply. My bad - don't know where to delete this answer.