It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

JQL Query for Summing Completed Story Points Per Assignee

With the following JQL Query, per assignee, I am able to get the count of completed issues, where story points are greater than zero: 

https://upvate.atlassian.net/rest/api/2/search?jql=sprint="21" and assignee="Chris Mazzochi" and "Story Points" > 0 and resolution="done"

But how do I get the result to return the total, of those story points, for the completed issues?

Thank you, 
C. Mazzochi

 

 

6 answers

2 accepted

5 votes
Answer accepted
Andy Heinzer Atlassian Team Apr 05, 2019

Hi everyone,
In an effort to help other users that find this question in search results, I hope this answer better explains the possible solutions here.

While Jira cannot natively return this data directly via JQL in the issue navigator page, there are a few different possible solutions

1) You could run the JQL query for each assignee, then export that data to CSV, and then use a sum function in a program like Excel or Google Sheets to find this info. Admittedly this method is slow, and tedious, especially if you need to do this for several assignees.

2) You could use a pie chart gadget in Jira to try to visualize data. Tomas Vrabec posted a solution on how he did this:

Hi there,

founded built-in solution.

Its a workaround, but its working.

1) Create JQL filter returning issues you would like to sum

2) Create dashboard

3) Add "Workload Pie Chart" Gadget

4) Set it for that created filter & estimations you would like to see.

Works for me.

 

Snímek obrazovky 2017-08-14 v 14.06.30.png

 

3) A third party plugin to Jira could help here. For example, Scriptrunner does have an aggregateexpression that can be used here to sum values like this and return them in the JQL results. https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_aggregateexpression has more details, however I think you could use an expression like

sprint="21" and assignee="username1" and issueFunction in aggregateExpression("Total Estimate for all Issues", "originalEstimate.sum()", "Remaining work", "remainingEstimate.sum()")

this would show all the issues in that sprint assigned to that user and then give a sum of the original estimates and the sum of the remaining estimates.

Although I've shown scriptrunner can help here, I'm sure there are other possible solutions for this same kind of problem provided by other plugin vendors on Marketplace as well https://marketplace.atlassian.com

I hope this helps.

Andy

In 2, it's not possible to pick story points as a "Time field to report on". :-/

Like Adrian_Procopiou likes this
0 votes
Answer accepted

Hi Chris,

JQL does not currently have a SUM function.  In order to sum totals you would need to utilize an add-on such as ScriptRunner.

Take a look at the Communities post titled Calculate sum of values and Sum of original estimate in issues by filter for more information and suggestion by users of the ScriptRunner add-on.

Cheers,

Branden

Thank you for replying Branden.  I've noted what you said. 

Thanks,
CM

Using this addon https://marketplace.atlassian.com/1218767 you can use SQL like language and have your report sthg like this

SELECT 
JQ.Assignee, 
SUM(JQ.StoryPoints)
FROM TABLE('Assignee, StoryPoints', JQL('sprint="21" and "Story Points" > 0 and resolution="done"olution="done"')) JQ
GROUP BY JQ.Assignee

Hi. I found this thread while looking for a way to sum story points. 
I have now creates several filters. e.g. 

resolved >= -2w AND "Agile Team" = "Data Warehouse" AND type in (Story, Bug) AND issueFunction in aggregateExpression(Effort, "effort.sum()")
resolved >= -2w AND "Agile Team" = "{temp name}" AND type in (Story, Bug) AND issueFunction in aggregateExpression(Effort, "effort.sum()") 

basically a filter for each team. I would not like to represent this in a nice way.. like in one gadget or something. 

! We are using Kanban so no sprints

Thanks for this

The "Sprint Health Gadget" that can be added to a dashboard adds the story points, provides a stat % for time elapsed, work complete and scope change for the current sprint. 

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you