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

0 vote
Alexey Matveev Community Champion Jan 21, 2018

Hello,

It is impossible to create such a report with JQL

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.

Suggest an answer

Log in or Join to answer
Community showcase
Teodora [Botron]
Published Feb 15, 2018 in Marketplace Apps

Jira Inferno: The Nine Circles of Jira Administration Hell

If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...

1,180 views 6 19
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot