Scripted Field to rollup sum of custom field from all child Epics to their Portfolio Parent Feature

[Andy Ukasick]

Hi - I'm NOT a developer, but I've been able to cobble together scriptrunner scripted fields for an Epic which contain the sum of all their childrens Story Points.  And another scripted field that rolls up the sum of all the Epic childrens completed Story Points. And another that calculates the percent done.  I'll share them below in case they may be of help to anyone.

Our hierarchy is Story --> Epic --> Feature --> Capability

Where I'm now stuck and need help is that I can't figure out how to create scripted fields that will roll those Story Point values up to the Feature level and the Capability level.  For example, if a Feature has three child Epics and each child Epic has three Stories and Each Story has 3 Story points, then the Feature should show that it has 27 Story points that roll up under it.  The Epics under a Feature are identifiable by the Feature's key being in the Epic's 'Parent Link' field. I've searched and searched and i cannot find any examples of this.

Here are the three scripts I created for Epics... now how could I do something similar to roll up Story Point values to the Features and Capabilities??

Script 1

//Rolls up Story Points to Epic level
import com.atlassian.jira.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.component.ComponentAccessor;

def issueLinkManager = ComponentAccessor.getIssueLinkManager()
def cfManager = ComponentAccessor.getCustomFieldManager()
double totalSP = 0
customField = ComponentAccessor.getCustomFieldManager().getCustomFieldObject("customfield_10106");

if (issue.getIssueTypeId() != "10000") {
return null
}
issueLinkManager.getOutwardLinks(issue.id)?.each {issueLink ->;
if (issueLink.issueLinkType.name == "Epic-Story Link" ) {
double SP = (double)(issueLink.destinationObject.getCustomFieldValue(customField) ?: 0)
totalSP = SP + totalSP;

}}
return totalSP

Script 2

//Rolls up Completed Story Points to the Epic Level

import com.atlassian.jira.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.component.ComponentAccessor;

def issueLinkManager = ComponentAccessor.getIssueLinkManager()
def cfManager = ComponentAccessor.getCustomFieldManager()
double completeSP = 0
customField = ComponentAccessor.getCustomFieldManager().getCustomFieldObject("customfield_10106");
enableCache = {-> false}

if (issue.getIssueTypeId() != "10000") {
return null
}
issueLinkManager.getOutwardLinks(issue.id)?.each {issueLink ->;
if (issueLink.issueLinkType.name == "Epic-Story Link" && (issueLink.destinationObject.getStatus().name == "Accepted" 
|| issueLink.destinationObject.getStatus().name == "Closed" 
|| issueLink.destinationObject.getStatus().name == "Done" ) ) {
double SP = (double)(issueLink.destinationObject.getCustomFieldValue(customField) ?: 0)
completeSP = SP + completeSP;
}}
return completeSP

Script 3

//Calculates percent complete of Story Points at the Epic level
import com.atlassian.jira.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.component.ComponentAccessor;

def issueLinkManager = ComponentAccessor.getIssueLinkManager()
def cfManager = ComponentAccessor.getCustomFieldManager()
double totalSP = 0
double completeSP = 0
double progressSP = 0
customField = ComponentAccessor.getCustomFieldManager().getCustomFieldObject("customfield_10106");
enableCache = {-> false}

if (issue.getIssueTypeId() != "10000") {
return null
}
issueLinkManager.getOutwardLinks(issue.id)?.each {issueLink ->;
if (issueLink.issueLinkType.name == "Epic-Story Link" ) {
double SP = (double)(issueLink.destinationObject.getCustomFieldValue(customField) ?: 0)
totalSP = SP + totalSP;
}

 if (issueLink.issueLinkType.name == "Epic-Story Link" && (issueLink.destinationObject.getStatus().name == "Accepted" 
|| issueLink.destinationObject.getStatus().name == "Closed" 
|| issueLink.destinationObject.getStatus().name == "Done" ) ) {
double CSP = (double)(issueLink.destinationObject.getCustomFieldValue(customField) ?: 0)
completeSP = CSP + completeSP;
}}

progressSP = completeSP / totalSP *100
return progressSP.round()

Any help is GREATLY APPRECIATED!  :)

[Ram Kumar Aravindakshan _Adaptavist_ · Accepted solution]

Hi @Andy Ukasick

You could try a Scripted Field, which filters Epic, Initiative, and Capability issues for your requirement.

Below is a sample working code for your reference:-

import com.atlassian.jira.component.ComponentAccessor

def issueLinkManager = ComponentAccessor.issueLinkManager
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def customFieldManager = ComponentAccessor.customFieldManager

def storyPoints = customFieldManager.getCustomFieldObjectsByName('Story Points')[0]
def totalStoryPoints = customFieldManager.getCustomFieldObjectsByName('Total Story Points')[0]

def linkedCollection = issueLinkManager.getLinkCollection(issue, loggedInUser,false)
def linkedIssues = linkedCollection.getOutwardIssues('Parent-Child Link')

def total = [] as List<Long>
def output = 0 as Long

if (issue.issueType.name == 'Epic') {
    def links = issueLinkManager.getOutwardLinks(issue.id)
    links.findAll {
        def name = it.issueLinkType.name
        def epicIssues = it.destinationObject
        if (name == 'Epic-Story Link' && epicIssues.getCustomFieldValue(storyPoints) != null) {
            output = epicIssues.getCustomFieldValue(storyPoints) as Long
            total.add(output)
        }
    }
} else if (issue.issueType.name in ['Initiative','Capability']) {
    linkedIssues.findAll {
        if (it.getCustomFieldValue(totalStoryPoints) != null) {
            output = it.getCustomFieldValue(totalStoryPoints) as Long
            total.add(output)
        }
    }
}
total.sum()

Please note, this sample code is not 100% exact to your environment. Hence, you will need to make the required modifications.

Below is a print screen of the Scripted Field configuration:-

To get the total of Story Points for the Epic, you can search for the Linked issues using the Epic-Story Link and for the Initiative and Capability issues, you can use the Parent-Child Link condition.

Below are a few test print screens for your reference:-

1) The Epics will calculate the total Story Points from the issues in the Epic, accumulate the total and display the value in the Total Story Points Scripted field as shown below:-

For the 1st Epic:-

The story points for the Issues in the first Epic are shown in the print screens below:-

For the second Epic:-

The story points for the Issues in the second Epic are shown in the print screens below:-

2) Next, Initiative will accumulate the Story Points from all the Epics linked to it, sum up the total and display the value in the Total Story Points Scripted field as shown below:-

3) And finally, the Capability will accumulate Story Points from all the Initiatives, sum up the total and display the value in the Total Story Points Scripted field as shown below:-

I hope this helps to answer your question. :)

Thank you and Kind Regards,

Ram

[Andy Ukasick]

@Ram Kumar Aravindakshan _Adaptavist_ 

Hi Ram

First  THANK YOU fortaking so much time and putting in so much effort to provide a solution.  I took what you provided and it most definitely works! 

However, I've observed some of our scrum teams occasionally entering Story Point values with decimals, like "2.5" for example (bad practice, I know).  The Long field type seems to simply truncate the decimal.  If it rounded it, that would have been fine, but it doesn't.  So I tried to take what you did and adapt it to work with "double" type fields. 

Next, I found that in cases where the entire list was null (no stories had story points) I'd get errors and the field simply didn't populate.  In such cases I wanted it to return a value of "0".  So I made an adaptation for that.

The scripts I ended up with for "Total Story Points" and "Completed Story Points" (two different scripted fields) seem to work well (so far!) and are provided below.  Where I now find myself hopelessly stuck is with the 3rd script which is for the "Total Story Point Progress (%)" scripted field.  This script is the third one provided below.  That script seems to work well with one BIG exception.  It returns accurate values for "Total Story Points" and for "Total Completed Story Points".  But when I try to perform any kind of math with those values everything goes wonky.  For example, if I try to divide the completed story points by the total story points and multiply by 100 (to get the % progress toward completion) it will always say that the result is 100%.  If (for ex) I have total story points = 25 and completed story points = 10 and I simply try to add them, it will say the reult is 70 instead of 35.  And and every sort of mathematical formula I try results in a remarkly WRONG answer.  I ran this past a couple actual java developers and after trying all sorts of things, they ultimately gave up and left scratching their heads as well.  BUT... I have every confidence that you could figure this o ut  :)

Here are the three scripts:

Total Story Points

import com.atlassian.jira.component.ComponentAccessor

def issueLinkManager = ComponentAccessor.issueLinkManager
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def customFieldManager = ComponentAccessor.customFieldManager

def storyPoints = customFieldManager.getCustomFieldObjectsByName('Story Points')[0]
def totalStoryPoints = customFieldManager.getCustomFieldObjectsByName('Total Story Points')[0]

def linkedCollection = issueLinkManager.getLinkCollection(issue, loggedInUser,false)
def linkedIssues = linkedCollection.getOutwardIssues('Parent-Child Link')

def total = [] as List
def totalOutput = 0 as double
def totalSP = 0 as double

if (issue.issueType.name == 'Epic') {
   def links = issueLinkManager.getOutwardLinks(issue.id)
   links.findAll {
     def name = it.issueLinkType.name
     def epicIssues = it.destinationObject
     if (name == 'Epic-Story Link' && epicIssues.getCustomFieldValue(storyPoints) != null) {
        totalOutput = epicIssues.getCustomFieldValue(storyPoints) as double
        total.add(totalOutput)
     }
   }
} else if (issue.issueType.name in ['Feature','Capability']) {
    linkedIssues.findAll {
       if (it.getCustomFieldValue(totalStoryPoints) != null) {
          totalOutput = it.getCustomFieldValue(totalStoryPoints) as double
          total.add(totalOutput)
     }
   }
}

totalSP = total.sum()

if (total == null || total.isEmpty()) { 
totalSP = 0 }

return totalSP

Total Completed Story Points

import com.atlassian.jira.component.ComponentAccessor

def issueLinkManager = ComponentAccessor.issueLinkManager
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def customFieldManager = ComponentAccessor.customFieldManager

def storyPoints = customFieldManager.getCustomFieldObjectsByName('Story Points')[0]
def totalCompletedStoryPoints = customFieldManager.getCustomFieldObjectsByName('Total Completed Story Points')[0]

def linkedCollection = issueLinkManager.getLinkCollection(issue, loggedInUser,false)
def linkedIssues = linkedCollection.getOutwardIssues('Parent-Child Link')

def complete = [] as List
def completeOutput = 0 as double
def completeSP = 0 as double

if (issue.issueType.name == 'Epic') {
   def links = issueLinkManager.getOutwardLinks(issue.id)
   links.findAll {
      def name = it.issueLinkType.name
      def status = it.destinationObject.getStatus().name
      def epicIssues = it.destinationObject
      if (name == 'Epic-Story Link' 
         && (status == "Accepted" || status == "Closed" || status == "Done")
         && epicIssues.getCustomFieldValue(storyPoints) != null) {
         completeOutput = epicIssues.getCustomFieldValue(storyPoints) as double
         complete.add(completeOutput)
     }
   }
} else if (issue.issueType.name in ['Feature','Capability']) {
    linkedIssues.findAll {
       if (it.getCustomFieldValue(totalCompletedStoryPoints) != null) {
         completeOutput = it.getCustomFieldValue(totalCompletedStoryPoints) as double
         complete.add(completeOutput)
     }
   }
}

completeSP = complete.sum()
 if (complete == null || complete.isEmpty()) { 
    completeSP = 0 }

return completeSP

Total Story Point Progress (%)

import com.atlassian.jira.component.ComponentAccessor;

def issueLinkManager = ComponentAccessor.issueLinkManager
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def customFieldManager = ComponentAccessor.customFieldManager

def storyPoints = customFieldManager.getCustomFieldObjectsByName('Story Points')[0]
def totalStoryPoints = customFieldManager.getCustomFieldObjectsByName('Total Story Points')[0]
def totalCompletedStoryPoints = customFieldManager.getCustomFieldObjectsByName('Total Completed Story Points')[0]

def linkedCollection = issueLinkManager.getLinkCollection(issue, loggedInUser,false)
def linkedIssues = linkedCollection.getOutwardIssues('Parent-Child Link')

def total = [] as List
def complete = [] as List
def totalOutput = 0 as double
def completeOutput = 0 as double
def totalPoints = 0 as double
def totalCompleted = 0 as double
def completeSP = 0
def totalSP = 0
def progressSP = 0

if (issue.issueType.name == 'Epic') {
   def links = issueLinkManager.getOutwardLinks(issue.id)
   links.findAll {
      def name = it.issueLinkType.name
      def epicIssues = it.destinationObject
      if (name == 'Epic-Story Link' && epicIssues.getCustomFieldValue(storyPoints) != null) {
      totalOutput = epicIssues.getCustomFieldValue(storyPoints) as double
      total.add(totalOutput)
     }
   }
} else if (issue.issueType.name in ['Feature','Capability']) {
    linkedIssues.findAll {
       if (it.getCustomFieldValue(totalStoryPoints) != null) {
          totalOutput = it.getCustomFieldValue(totalStoryPoints) as double
          total.add(totalOutput)
     }
   }
}

totalSP = total.sum()

if (total == null || total.isEmpty()) { 
    totalSP = 0 }

if (issue.issueType.name == 'Epic') {
    def links = issueLinkManager.getOutwardLinks(issue.id)
    links.findAll {
       def name = it.issueLinkType.name
       def status = it.destinationObject.getStatus().name
       def epicIssues = it.destinationObject
       if (name == 'Epic-Story Link' 
          && (status == "Accepted" || status == "Closed" || status == "Done")
          && epicIssues.getCustomFieldValue(storyPoints) != null) {
          completeOutput = epicIssues.getCustomFieldValue(storyPoints) as double
          complete.add(completeOutput)
     }
   }
} else if (issue.issueType.name in ['Feature','Capability']) {
    linkedIssues.findAll {
       if (it.getCustomFieldValue(totalCompletedStoryPoints) != null) {
          completeOutput = it.getCustomFieldValue(totalCompletedStoryPoints) as double
          complete.add(completeOutput)
     }
   }
}

completeSP = complete.sum()

if (complete == null || complete.isEmpty()) { 
   completeSP = 0 }

progressSP = ((completeSP / totalSP) * 100)
return progressSP

Help :)

-Andy

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Andy Ukasick

For your First Scripted field, you could simplify the code by adding the else conditions to return 0 if no Story Points were included, as shown in the example below:-

import com.atlassian.jira.component.ComponentAccessor

def issueLinkManager = ComponentAccessor.issueLinkManager
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def customFieldManager = ComponentAccessor.customFieldManager

def storyPoints = customFieldManager.getCustomFieldObjectsByName('Story Points')[0]
def totalStoryPoints = customFieldManager.getCustomFieldObjectsByName('Total Story Points')[0]

def linkedCollection = issueLinkManager.getLinkCollection(issue, loggedInUser, false)
def linkedIssues = linkedCollection.getOutwardIssues('Parent-Child Link')

def total = [] as List<Double>
def output = 0.0 as Double

if (issue.issueType.name == 'Epic') {
    def links = issueLinkManager.getOutwardLinks(issue.id)
    links.findAll {
        def name = it.issueLinkType.name
        def epicIssues = it.destinationObject
        if (name == 'Epic-Story Link' && epicIssues.getCustomFieldValue(storyPoints) != null) {
            output = epicIssues.getCustomFieldValue(storyPoints) as Double
        } else {
            output = 0
        }
        total.add(output)
    }
} else if (issue.issueType.name in ['Initiative','Capability']) {
    linkedIssues.findAll {
        if (it.getCustomFieldValue(totalStoryPoints) != null) {
            output = it.getCustomFieldValue(totalStoryPoints) as Double
        } else {
            output = 0
        }
        total.add(output)
    }
}
total.sum()

For your second code, you can follow the same step and the example above but include the second scripted field and also the Done filtration to identify completed story points as shown below:-

import com.atlassian.jira.component.ComponentAccessor

def issueLinkManager = ComponentAccessor.issueLinkManager
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def customFieldManager = ComponentAccessor.customFieldManager

def storyPoints = customFieldManager.getCustomFieldObjectsByName('Story Points')[0]
def totalStoryPointsCompleted = customFieldManager.getCustomFieldObjectsByName('Total Completed Story Points')[0]

def linkedCollection = issueLinkManager.getLinkCollection(issue, loggedInUser, false)
def linkedIssues = linkedCollection.getOutwardIssues('Parent-Child Link')

def total = [] as List<Double>
def output = 0.0 as Double

if (issue.issueType.name == 'Epic') {
    def links = issueLinkManager.getOutwardLinks(issue.id)
    links.findAll {
        def name = it.issueLinkType.name
        def epicIssues = it.destinationObject
        if (name == 'Epic-Story Link' && epicIssues.getCustomFieldValue(storyPoints) != null && epicIssues.status.name == 'Done') {
            output = epicIssues.getCustomFieldValue(storyPoints) as Double
        } else {
            output = 0
        }
        total.add(output)
    }
} else if (issue.issueType.name in ['Initiative','Capability']) {
    linkedIssues.findAll {
        if (it.getCustomFieldValue(totalStoryPointsCompleted) != null) {
            output = it.getCustomFieldValue(totalStoryPointsCompleted) as Double
        } else {
            output = 0
        }
        total.add(output)
    }
}
total.sum()

And finally, to calculate the percentage of completed story points, you can directly do a calculation without having to go through the linked issues as shown below:-

import com.atlassian.jira.component.ComponentAccessor

def customFieldManager = ComponentAccessor.customFieldManager

def totalStoryPoints = customFieldManager.getCustomFieldObjectsByName('Total Story Points')[0]
def totalStoryPointsCompleted = customFieldManager.getCustomFieldObjectsByName('Total Completed Story Points')[0]

def total = [] as List<Double>
def totalPoints = issue.getCustomFieldValue(totalStoryPoints) as Double
def totalCompleted = issue.getCustomFieldValue(totalStoryPointsCompleted) as Double
def output = 0.0 as Double

if(issue.getCustomFieldValue(totalStoryPoints) != null && issue.getCustomFieldValue(totalStoryPointsCompleted) != null) {
    if (issue.issueType.name == 'Epic') {
        output = (totalCompleted / totalPoints) * 100 as Double
    } else if (issue.issueType.name in ['Initiative','Capability']) {
        output = (totalCompleted / totalPoints) * 100 as Double
    }
    total.add(output)
} else {
    total.add(output)
}

total.sum()

Please note, the sample codes provided are not 100% exact to your environment. Hence you will need to make the required modifications.

Below are some example print screens of the Total Completed and Percentage of Completion calculation:-

1) On the Epic with Completed Stories:-

2) On the Epic with Incomplete Stories:-

3) On this Initiative which calculates the Total and Average of both the previous Epics:-

4) On the Capability Issue with the Percentage calculation of the Initiative:-

I hope this helps to answer your question. :)

Thank you and Kind Regards,

Ram

[Andy Ukasick]

@Ram Kumar Aravindakshan _Adaptavist_ 

Ram!  Thank You!!  This works perfectly!  :-)

I did make a couple small tweaks.  On Features and Capabilities (above Epic level) if there were no Story Points or no Completed Story Points, at all (i.e. null or [ ] ), then the field would be blank and I'd prefer that in those cases the field be populated with a "0".  The 'else' statements that you added only worked on Epics (on Epics the field would contain a "0", but on the levels above an Epic they'd be blank (when there were no story points).  So I just added this at the very bottom of the 'Total Story Points' and 'Total Completed Story Points' scripts.  I'm sure you'd have a more elegant solution, but it works:

def totalSP = total.sum()
if (total == null || total.isEmpty()) { 
   totalSP = 0 }

return totalSP

I made a slight change to the 'Total Story Point Progress (%)' field script as well.  I wanted the result to be rounded to zero decimal places.  Here's what I came up with to accomplish that.  Again... there's probably a smoother way to do it, but it worked.  I changed this section from this:

if(issue.getCustomFieldValue(totalStoryPoints) != null && issue.getCustomFieldValue(totalStoryPointsCompleted) != null) {
    if (issue.issueType.name == 'Epic') {
        output = (totalCompleted / totalPoints) * 100 as Double
    } else if (issue.issueType.name in ['Feature','Capability']) {
        output = (totalCompleted / totalPoints) * 100 as Double
    }
    total.add(output)
} else {
    total.add(output)
}

To this (note the 'output = output.round()' statements added):

if(issue.getCustomFieldValue(totalStoryPoints) != null && issue.getCustomFieldValue(totalStoryPointsCompleted) != null) {
    if (issue.issueType.name == 'Epic') {
        output = (totalCompleted / totalPoints) * 100 as Double
        output = output.round()
    } else if (issue.issueType.name in ['Feature','Capability']) {
        output = (totalCompleted / totalPoints) * 100 as Double
        output = output.round()
    }
    total.add(output)
} else {
    total.add(output)
}

Once again, thank you so very much for kindly taking so much of your time to assist me!  I must say, of all the Jira add-ons, I can't think of any more critical to have than 'ScriptRunner'.  Well done!