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

[Christian Schlaefcke]

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

[Christian Schlaefcke · Accepted solution]

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

[Christian Schlaefcke]

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

[Administrator]

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 ;-)

[Administrator]

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"

[Christian Schlaefcke]

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

[Felix Gebhard]

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

[Administrator]

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 ;-)

[Christian Schlaefcke]

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

[Christian Schlaefcke]

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