Automation Rule - calculate progress based on children of linked epics

kenneth.gomes October 11, 2022

I am trying to create an automation between two projects. The current JIRA hierarchy we follow is as below;

Portfolio Epic > Epic > User Story/Task

Problem Statement:

I have two projects Project A and Project B

  • Project A - Portfolio Epic
    • Epic 1
    • Epic 2
    • Epic 3
  • Project B - Portfolio Epic 
    • Epic 1
    • Epic 2
    • Epic 3

I have an Epic in Project A - Epic 1 that is linked via "Related To" link to the overall Portfolio Epic in Project B. Both projects have two different project keys.

I am trying to set up an automation that looks at Epic 1 (Project A) and based on the "Relates To" link picks up the children in Project B - Portfolio Epic and sums up the story points and the % complete on children tickets and writes it back to Project A - Epic 1.

 

What I have got so far:

JIRA Automation 1.PNG

Currently the Automation is manually triggered while I test it out and I will move it to scheduled or auto triggered once I have it to where I need it.

Story points is calculated using the following - {{lookupIssues.Story Points.sum}}

At this point although the above rule is supposed to work it returns no value as I suspect the following may be blocking it. This automation only works if the rule is global; as project specific rules restrict the look up clause above. I need help validating my theory.

Second part to my problem is that I am unable figure out if there is a way to get the progress % over to Project A - Epic 1 from Project B

JIRA Automation 2.PNG

Any help and/or guidance would be greatly appreciated.

2 answers

2 accepted

1 vote
Answer accepted
Mark Segall
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 11, 2022

Hi @kenneth.gomes - You are correct that this needs to be a multiple project rule scope to span across your two projects.  Once the scope is updated, you should have no issue with your second problem.

Be warned though that Relates To is a nasty link type when you're trying to automate because there is no "parent/child" relationship as you have with Blocks/Is Blocked By and Clones/Is Cloned By link types.  This could cause some pain points in your automation.

kenneth.gomes October 11, 2022

Thank you for helping me validate my theory

Like Mark Segall likes this
0 votes
Answer accepted
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 11, 2022

Hi @kenneth.gomes 

Your hypothesis is correct about the rule scope: a lookup issues action (or branch, etc.) can only see the issues within the selected scope of the rule.

Next regarding the % progress...first how do you want to measure progress?  The image you show is of a parent and child issues.  That progress indicator is by the status category of issues.  There are other ways to measure progress, such as based on the aggregate of your estimation values.

Once you have selected your progress method, you could use the issues in the lookup issues and smart value list filtering (or separate lookup calls) to compute the result.

Kind regards,
Bill

kenneth.gomes October 11, 2022

Hi Bill,

Could you point me to the function that would help me aggregate the estimation values?

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 11, 2022

Certainly!  Here are a couple of examples, using the math functions and smart value list filtering, with references below...

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/

https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588

 

For the same percentage the parent/child shows based on count:

{{#=}}ROUND((0+{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}1+{{/}}{{/}}0)/{{lookupIssues.size|0}}*100,0){{/}}

To instead do this by story points, try this:

{{#=}}ROUND((0+{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}{{Story points|0}}+{{/}}{{/}}0)/{{lookupIssues.Story points.sum|0}}*100,0){{/}}

 

How these work...

  • using a lookup of all of the issues
  • first use smart value list filter to find the ones with a Status Category of done; for those items, add a value followed by a plus sign
  • the above value is wrapped in parenthesis, so it can be divided by the total for the entire lookup result
  • that value is then multiplied by 100 and rounded so it appears as a percentage, 0 to 100
  • finally everything is wrapped in the math operators
  • note inside of the rounding operation...an extra "0+" and trailing "0" are added.  This handles the case when the lookup returns nothing.
Like kenneth.gomes likes this
kenneth.gomes October 26, 2022

Hi Bill,

I got access to run the rule globally but for some reason the story points are not being summed up. I haven't used your formula yet but instead I tried to keep it simple by using {{lookupissues.Story Points.sum}} but the automation still wont sum up the story points.

Log

JIRA Automation 3.PNG

Based on the automation it is able to pick up the linked PE but it is unable to sum up the epics underneath it.

CL - 6372 is a Epic

UIP-6 is a Portfolio Epic

I am trying to sum up the child issues underneath them.

JIRA Automation 4.PNG

Any suggestion or help would be greatly appreciated.

Thanks

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 26, 2022

I notice two things: there is an extra space before the smart value and the case is incorrect.  FYI that smart values are name, spacing, and case sensitive.

Please try this one if you are using a company-managed project (CMP):

{{lookupIssues.Story points.sum|0}}

If instead you are using a team-managed project (TMP), they use Story point estimate:

{{lookupIssues.Story point estimate.sum|0}}

I also added the default value of zero (0) so even if the fields are empty you will get a number.

kenneth.gomes October 26, 2022

Hi Bill,

I tried the changes you recommended but I am still unable to display the sum. 

JIRA Automation 5.PNG

I tried everything at this point I am not sure what is wrong with the automation.

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 26, 2022

Please post an image of your complete, current rule.  That may help to explain what is happening.  Thanks!

kenneth.gomes October 26, 2022

Hi Bill,

Here is the complete rule;

JIRA Automation 1.PNG

I trigger it manually from this epic

JIRA Automation 6.PNG

The look up is able to look up the related to ticket i.e. UIP-6

JIRA Automation 4.PNG

But it is unable to lookup the children within UIP-6 and sum up the SP values.

kenneth.gomes October 26, 2022

Hi Bill,

I did some more research and it seems the above formula only works for regular projects not next gen projects. It seems I would need to look up how to automation rules work for next gen projects

Ken

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 26, 2022

Did you see my note about using "Story point estimate" rather than "Story points"?  That should work for a team-managed project (formerly called NextGen).

{{lookupIssues.Story point estimate.sum|0}}

Next, you appear to be trying to sum child issues of the epic, not the linked issues.

Please try changing the Lookup Issues JQL to  this:

parent = {{triggerIssue.key}}

Like Serge Kony likes this
Coco S Chaussée
Contributor
November 28, 2023

@Bill Sheboy - 

I have been struggling with a similar problem...

This is for company-managed projects.  I want to calculate % complete on Epics based on the number of children.  I have created an automation rule using your magic formula 

{{#=}}ROUND((0+{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}1+{{/}}{{/}}0)/{{lookupIssues.size|0}})*100,0){{/}}

but get this error

Capture.JPG

Your help would be greatly appreciated.

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 28, 2023

Hi @Coco S Chaussée 

Looking at your expression, there is a parenthesis in the wrong place.  It appears my original post had an error, and I have corrected that.  Tip: it helps to create expressions like this in a text editor which shows matched / mismatched parentheses and brackets.

Here is the corrected one:

{{#=}}ROUND((0+{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}1+{{/}}{{/}}0)/{{lookupIssues.size|0}}*100,0){{/}}

 

It can also help to build expressions like this, piece by piece, and then combine them.  Here is an updated version of how I did that (except with the plus signs added at the front rather than behind the ones).

Completed issues count:

( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} )

Total issues count:

{{lookupIssues.size|0}}

The percentage divides those two values:

{{#=}}( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100{{/}}

Finally, the percentage, rounded to a whole number would be:

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}

 

Kind regards,
Bill

Like # people like this
Coco S Chaussée
Contributor
November 29, 2023

Perfect!  It works!!! 

Thank you so much for sharing your knowledge and for a quick turn-around.

Like Bill Sheboy likes this
Coco S Chaussée
Contributor
December 4, 2023

@Bill Sheboy - is there a limit on the number of children returned?  I stumbled on an epic that has over 200 stories, but the formula only returns 100.

Any way to work around this?

Thank you

Coco

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 4, 2023

There is no way I know of around that: there is an automation rule limit of up to 100 issues for lookup issues and other aspects of rules have other limits: https://community.atlassian.com/t5/Jira-articles/Automation-for-Jira-Send-web-request-using-Jira-REST-API/ba-p/1443828

If you often expect more than 100 issues, you may need to define a predictable way to break this up into pieces, with additional JQL criteria, save the results in variables, and sum up later.  For example, by labels, components, etc.

Erin Ehlers April 24, 2024

Duplicative post.

Erin Ehlers April 24, 2024

HI @Bill Sheboy ! 

Thank you so much for explaining how you got 

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}

I am trying to also remove any issues in status "Not going to do" or "Cancelled" from the equation all together but having a hard time getting the syntax correct. Any help would be greatly appreciated! 

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 24, 2024

HI @Erin Ehlers -- Welcome to the Atlassian Community!

First thing...for older threads it can help to create a new question and add a link to original ones.  That helps ensure more people see the question to offer suggestions.

Back to your question...

You can use various conditional logic functions to do that, such as not() and and() .  I recommend doing this step by step to get the logic how you want it, testing as you go.  For example:

and( not(equals(status.name, "Not going to do")), not(equals(status.name, "Cancelled")) )

Then that can be used to substitute into then entire expresssion:

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(and( not(equals(status.name, "Not going to do")), not(equals(status.name, "Cancelled")) ))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}

 

Please check the case of your status names match the expression exactly, or add the toLowerCase() function force them to a known value.

Kind regards,
Bill

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events