JQL Query for Summing Completed Story Points Per Assignee

[Ron Jacob]

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

[Thomas Brightwell]

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.

[Thomas Brightwell]

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

[Thomas Brightwell]

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.

[Marcus Andrews]

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.

[Thomas Brightwell]

Is your Story Points field a Text field?

Mine is a Number Field. If I swap my script over to use a Text Field (with numeric data, and the occasional blank string) then I can trigger the same error message as you get.

If it is a Text field, then you can potentially try checking for empty strings and the like.

[Alexey Matveev]

Hello,

It is impossible to create such a report with JQL