JQL Query for Summing Completed Story Points Per Assignee

[Chris Mazzochi]

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

[Andy Heinzer · Accepted solution]

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.

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

[Joris Vleminckx]

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

[somethingblue · Accepted solution]

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

[Chris Mazzochi]

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

Thanks,
CM

[marish sharp]

Needed one

[Piotr Bojko]

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

[Andrew Mallonee]

It is a bit crazy to me that this is not natively available in Jira. I do want to point out for people finding this thread that "aggregateExpression" does not work in Script Runner for Jira CLoud. I have yet to find an alternative and purchasing some other add-on for something Jira should natively support is not an option for me.

[Aron Gombas _Midori_]

You can calculate this and similar aggregations with the Better Excel Exporter app.

The use case is mentioned is among the app's sample exports:

[Kristian Lind]

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

[Deleted user]

Hey @Kristian Lind I am trying to do the same for my project and this is my JQL query - project = Support AND createdDate >= startOfWeek() AND Organizations = "ABC" AND (Organizations, "organizations.sum()")

Can you please advise if I am doing it wrong as this doesn't seem to work?

[Marlene Kegel - codefortynine]

Hello,

I am Marlene from codefortynine.

Another option to sum-up story points would be our app Quick Filters for Jira Dashboards.

With our app you can aggregate any numeric field with the Quick Issue Statistics gadget, the Quick Two Dimensional Filter Statistics gadget and the Pie Chart gadget.

After installation you simply:

• Navigate to your Jira Dashboard
• Add one of the gadgets mentioned above
• Configure the gadget as any Jira standard gadget
• Select "Story Points" as value shown

You can check out our app without installation on our demo dashboards.

[Vasile Tomoiaga]

Since I could not find a simple and free solution I wrote a user script, find it here: https://github.com/tomoiaga/user-scripts/blob/master/JiraQueryFilterSum.user.js

The script will automatically create a SUM column at the top of a query filter or stories in an epic or a dashboard gadget, and will sim any numeric column.

You need Greasemonkey or Tampermonkey to install it, some instructions here:

https://www.linkedin.com/pulse/i-needed-way-sum-effort-points-jira-didnt-find-any-so-vasile-tomoiag%25C4%2583/?trackingId=7AJ5cIIialgQKQpZdLuoEQ%3D%3D

[Vasile Tomoiaga]

Here is an example

[Elycia Abdala juco]

If the filter contains multiple sprints, are you able to sum totals based on sprints?  

[Neetu Verma]

@Vasile Tomoiaga - I downloaded Tampermonkey, installed the user script but I can't get it work. 

Any suggestions please.

[Sven Maack]

Thank you for the script. I had to add a selector

"'table[aria-label="Issues"]'," to the ISSUES_TABLES_SELECTOR: and than it worked perfectly.

For others who might have the same issue, I'm using JIRA Cloud

[Deb Laurendeau]

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. 

[adarshsridhara1]

Thanks for this