What would be the best way to get a cummulated work log summary

Hi,

one of my company s PO asked me if there is a way to sum up all efforts for a certain epic and all linked stories and sub-tasks.

I searched in the report area of the projects but there are only reports on a user or version basis available. I also tried to find a plugin but found nothing obviuos. I know I could probably do with JQL and MS Excel export, but the PO definetely wants an integrated solution.

We are using Jira Agile and Tempo. I hope that somebody can point me in the right direction. Any help is very appreciated.

Best Regards,

Christian

7 answers

1 accepted

Hi All,

I actually found a solution myself. I played around with the Script Runner Plugin/Scripted Custom Field - thank you Jamie for this Swiss Army Knife for JIRA :-)

I just wrote a script that crawls over a specific link type ("has Story") linked issues and their sub tasks summing up the time spent fields if any work logs have been provided.

It is a little bit hard-coded at the moment so there still is room for improvement:

import com.atlassian.jira.ComponentManager

def componentManager = ComponentManager.getInstance()
def issueLinkManager = componentManager.getIssueLinkManager()

def totalTimeSpent = 0

def resultHTML = "<ul>"

def epicTimeSpentString = "No Work Logs"
def epicTimeSpent = issue.timeSpent
if(epicTimeSpent) {
    epicTimeSpentString = componentManager.getJiraDurationUtils().getFormattedDuration(epicTimeSpent)
    totalTimeSpent += epicTimeSpent
}
resultHTML += "<li><b>This " + issue.issueTypeObject.name + ": " + epicTimeSpentString + "</b></li>"

if(issue.getSubTaskObjects().size > 0) {
    resultHTML += "<ul>"
    issue.getSubTaskObjects().each {epicSubTask ->
        def epicSubTaskTimeSpentString = "No Work Logs"
        def epicSubTaskTimeSpent = epicSubTask.timeSpent
        if(epicSubTaskTimeSpent) {
            epicSubTaskTimeSpentString = componentManager.getJiraDurationUtils().getFormattedDuration(epicSubTaskTimeSpent)
            totalTimeSpent += epicSubTaskTimeSpent
        }
        resultHTML += "<li>" + epicSubTask.issueTypeObject.name + " - " + epicSubTask.key + " - " + epicSubTask.summary + " - <b>" + epicSubTaskTimeSpentString + "</b></li>"
    }
    resultHTML += "</ul>"
}

issueLinkManager.getOutwardLinks(issue.id).each {issueLink ->
    if (issueLink.issueLinkType.name == "Epic to User Story") {
        def linkedIssue = issueLink.destinationObject
        def linkedIssueTimeSpent = linkedIssue.timeSpent
        def linkedIssueTimeSpentString = "No Work Logs"
        if(linkedIssueTimeSpent) {
            linkedIssueTimeSpentString = componentManager.getJiraDurationUtils().getFormattedDuration(linkedIssueTimeSpent)
            totalTimeSpent += linkedIssueTimeSpent
        }
        //resultHTML += "<li>" + issueLink.issueLinkType.name + " - " +  linkedIssue.key + " - " + linkedIssueTimeSpentString +"<br/></li>"
        resultHTML += "<li>" + linkedIssue.issueTypeObject.name + " - " +  linkedIssue.key + " - " + linkedIssue.summary + " - <b>" + linkedIssueTimeSpentString +"</b></li>"

        if(linkedIssue.getSubTaskObjects().size > 0) {
            resultHTML += "<ul>"
            linkedIssue.getSubTaskObjects().each {linkedIssueSubTask ->
                def linkedIssueSubTaskTimeSpentString = "No Work Logs"
                def linkedIssueSubTaskTimeSpent = linkedIssueSubTask.timeSpent
                if(linkedIssueSubTaskTimeSpent) {
                    linkedIssueSubTaskTimeSpentString = componentManager.getJiraDurationUtils().getFormattedDuration(linkedIssueSubTaskTimeSpent)
                    totalTimeSpent += linkedIssueSubTaskTimeSpent
                }
                resultHTML += "<li>" + linkedIssueSubTask.issueTypeObject.name + " - " + linkedIssueSubTask.key + " - " + linkedIssueSubTask.summary + " - <b>" + linkedIssueSubTaskTimeSpentString + "</b></li>"
            }
            resultHTML += "</ul>"
        }
    }
}

resultHTML += "</ul><br/>"

def totalTimeSpentString = totalTimeSpent ? componentManager.getJiraDurationUtils().getFormattedDuration(totalTimeSpent) : "No Work Logs"
resultHTML += "<h4>" + totalTimeSpentString + "</h4>"

return resultHTML

I use two variants of this full output script to just provide a formatted string with the calculated sum and a plain number output so that further calculations are possible with escel exports.

I realize that this could produce a lot of output for complex relationships (one epic with a lot of stories and a lot of sub tasks) and that this could be quite permormance consuming. But in general it is doing what was requested and I will have an eye on performance issues. My PO is happy now :-)

Please feel free to re-use and let me know of any improvements! :-)

Best Regards,

Christian

Ah - and the Script Runner Plugin is also available ;-)

This might excatly what i was looking for. I try it out and give you some feedback how it worked for me.

Any chance you could post your second variant? Since i am lazy ;-)

Hi Riad,

this would be the version with the plain number:

import com.atlassian.jira.ComponentManager

import java.text.DecimalFormat

def componentManager = ComponentManager.getInstance()
def issueLinkManager = componentManager.getIssueLinkManager()

def totalTimeSpent = 0.0d

def epicTimeSpent = issue.timeSpent
if(epicTimeSpent) {
    totalTimeSpent += epicTimeSpent
}

if(issue.getSubTaskObjects() != []) {
    issue.getSubTaskObjects().each {epicSubTask ->
        def epicSubTaskTimeSpent = epicSubTask.timeSpent
        if(epicSubTaskTimeSpent) {
            totalTimeSpent += epicSubTaskTimeSpent
        }
    }
}

if(issueLinkManager.getOutwardLinks(issue.id) != []) {
    issueLinkManager.getOutwardLinks(issue.id).each {issueLink ->
        if (issueLink.issueLinkType.name == "Epic to User Story") {
            def linkedIssue = issueLink.destinationObject
            def linkedIssueTimeSpent = linkedIssue.timeSpent
            if(linkedIssueTimeSpent) {
                totalTimeSpent += linkedIssueTimeSpent
            }

            //if(linkedIssue.getSubTaskObjects().size > 0) {
            if(linkedIssue.getSubTaskObjects() != []) {
                linkedIssue.getSubTaskObjects().each {linkedIssueSubTask ->
                    def linkedIssueSubTaskTimeSpent = linkedIssueSubTask.timeSpent
                    if(linkedIssueSubTaskTimeSpent) {
                        totalTimeSpent += linkedIssueSubTaskTimeSpent
                    }
                }
            }
        }
    }
}

if(totalTimeSpent > 0) {
    totalTimeSpent = totalTimeSpent / 60.0d / 60.0d / 8.0d //Seconds to Hours
}

//formatNumber(number: totalTimeSpent, Locale.ENGLISH, format: '##0.0')
//new DecimalFormat("#.#").format(totalTimeSpent);

totalTimeSpent

Please keep in mind that this can cause high load on your jira instance - so handle with care! Meanwhile I would recomment evaluating the Structure-Plugin or EazyBI.

Kind Regards,

Christian

Hi Riad,

this would be the version with the plain number:

import com.atlassian.jira.ComponentManager

import java.text.DecimalFormat

def componentManager = ComponentManager.getInstance()
def issueLinkManager = componentManager.getIssueLinkManager()

def totalTimeSpent = 0.0d

def epicTimeSpent = issue.timeSpent
if(epicTimeSpent) {
    totalTimeSpent += epicTimeSpent
}

if(issue.getSubTaskObjects() != []) {
    issue.getSubTaskObjects().each {epicSubTask ->
        def epicSubTaskTimeSpent = epicSubTask.timeSpent
        if(epicSubTaskTimeSpent) {
            totalTimeSpent += epicSubTaskTimeSpent
        }
    }
}

if(issueLinkManager.getOutwardLinks(issue.id) != []) {
    issueLinkManager.getOutwardLinks(issue.id).each {issueLink ->
        if (issueLink.issueLinkType.name == "Epic to User Story") {
            def linkedIssue = issueLink.destinationObject
            def linkedIssueTimeSpent = linkedIssue.timeSpent
            if(linkedIssueTimeSpent) {
                totalTimeSpent += linkedIssueTimeSpent
            }

            //if(linkedIssue.getSubTaskObjects().size > 0) {
            if(linkedIssue.getSubTaskObjects() != []) {
                linkedIssue.getSubTaskObjects().each {linkedIssueSubTask ->
                    def linkedIssueSubTaskTimeSpent = linkedIssueSubTask.timeSpent
                    if(linkedIssueSubTaskTimeSpent) {
                        totalTimeSpent += linkedIssueSubTaskTimeSpent
                    }
                }
            }
        }
    }
}

if(totalTimeSpent > 0) {
    totalTimeSpent = totalTimeSpent / 60.0d / 60.0d / 8.0d //Seconds to Hours
}

//formatNumber(number: totalTimeSpent, Locale.ENGLISH, format: '##0.0')
//new DecimalFormat("#.#").format(totalTimeSpent);

totalTimeSpent

Please keep in mind that this can cause high load on your jira instance - so handle with care! Meanwhile I would recomment evaluating the Structure-Plugin or EazyBI.

Kind Regards,

Christian

Btw: Worked for me except that our Epic Links have different name. So i had to use the line

issueLink.issueLinkType.name == "Epic-Story Link"

Perfect! I am glad this helped you! :-)

Uh Thanks very much that was fast.

Yeah i figured that this link traversing might have performance issues. But having to count the worklog manually is a even bigger performance issue ;-)

Perfect! I am glad this helped you! :-)

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

349 views 1 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you