JQL Query for Summing Completed Story Points Per Assignee

How to create a report at the end of a sprint that lists each person and the number or points they completed in the sprint

2 answers

This widget could not be displayed.
Alexey Matveev Community Champion Jan 21, 2018

Hello,

It is impossible to create such a report with JQL

This widget could not be displayed.

Hi Shivi,

I have had some success using a Python script to run multiple JQL queries via JIRA's REST API on a per user basis, totalling story points over the past 14 days.

• JQL is an extension of SQL which is used by JIRA internally for querying its database.
• JIRA exposes a REST API for querying its database programmatically using JQL
• The API is well documented here
https://jira.readthedocs.io/en/master/examples.html

There is a JIRA Python library you can use to access this. I installed it via pip:
(From a command line on a pc with Python and pip and Idle installed)
pip install jira
idle
Then I made this script:

from jira import JIRA

options = {'server': 'http://my_jira_website',}
jira = JIRA(options, basic_auth=('admin_user', 'admin_password'))
users = ["user1", "user2"] #Add a list of users

print ("User \tStory Points")
for user in users:
story_sum = 0
my_query = jira.search_issues('status changed by ' + user + ' to "Done" after startOfDay(-14)')
for issue in my_query:
if issue.fields.customfield_10002 is not None:
story_sum = story_sum + issue.fields.customfield_10002

print (user + "\t" + str(story_sum))

Running that script generates

User       Story Points

user1     11.25

user2     8.0

which I can copy and paste into Excel. (Or write a bit of Python to do that for me)

Note the places in the script you would need to change:

  • the link to your own JIRA server's website
  • the user who will run the query
  • the list of users
  • the days back to go (14 for a two week sprint etc).
  • the query itself (you may want to query a particular project).

If you can get this running, then you can get clever, and run several queries per user and get a full blown report generated.

(On a final note, don't save your script as "JIRA.py" as this will confuse your REST API calls. Any other name is fine!)

I hope this helps.

P.S. If you hadn't guessed, customfield_10002 is "story points".

I tracked that one down using the JIRA REST API browser. Most other fields have friendly names such as timespent

To get Python, PIP, Idle and the JIRA REST API working on a Windows PC:

  • Download the latest Python from here

https://www.python.org/downloads/

  • Run the installer, being sure to add Python to your PATH (this makes things much easier!). It comes with PIP and Idle.
  • Open a Command Prompt with Administrative Privileges
  • Type pip install jira

which should add the JIRA REST API to your system.

  • Run Idle (this is a Python file editor; the installer should have added a shortcut to your Start Menu).
  • Create a new module using the menus.
  • Paste my script into the window that appears, changing fields as necessary.
  • Save it
  • Press F5 to run it. The output should appear in the original window.

(tested on an unlucky colleague's Windows 10 PC).

P.S. the "pip install jira" instruction is run from outside Python. It won't work from inside Idle! It also needs the PATH variable thing setting during install.

I am having trouble with your script.  Firstly, it seems that 10002 was returning the ticket ID (tested using print) and so falling over on summation (cant use operator + on int and str).  I went into Jira and Administration->Issues->CustomFields and hovering over the edit button for story points showed me the ID as 10006.

Changing the story_sum summation to point to 10006 seemed to work, at least for the first dev in the list, but fell over when it hit a None for story points (I had forgotten to change the "if not none" line to 10006.  However, when i did change that line it now falls over saying that "AttributeError: type object 'PropertyHolder' has no attribute 'customfield_10006'"

I'm confused why it can find it for the summation but not in the test for none...

Any advice?

Edit: It appears that this is a problem with my query.  When I use your original query with 10006 it works perfectly.  Simplified version of my query (to count total SP of any ticket user has worked on) is:  'assignee WAS ' + user.  It loops through a couple of the devs in the list fine, displaying totals and then fails with the above error.  Confused why it finds the attribute for a few hundred issues for the first three devs but not all.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Apr 22, 2018 in Jira Software

How-to setup a secured Jira Software 7.9.0 on Ubuntu 16.04.4 in less than 30 minutes

...PermissionsStartOnly=true User=www-data Group=www-data ExecStart=/opt/jira/bin/startup.sh ExecStop=/opt/jira/bin/shutdown.sh TimeoutStartSec=120 TimeoutStopSec=600 PrivateTmp=true [Install] WantedBy...

1,481 views 10 12
Read article

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