JQL to calculate sum of story points for all stories for a sprint

Mit Tolia April 26, 2022

Dear Team,

One of the teams I am working with, have got a unique requirement of showing the total number of story points for all the stories/Tasks slotted in various different future sprints.

The team have adopted SAFe practices & hence out of the PI planning, they have a rough sprint plan having stories and tasks estimated (in story points - using the story points field in Jira cloud) & slotted in future sprints.

The intent is to provide leadership with a view of total no. of story points planned for each sprint.

I am aware JQL - out of the box will not support this. Hence looking at a plugin like Script Runner/Enhanced Search but wanted to confirm that this does unlock the aggregate functions like Sum/Totals that I can use in writing advanced JQL queries?

if someone can redirect me to an example JQL that does a sum on story points field - that would be awesome

Thanks in advance.

Mit

5 answers

3 votes
Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 26, 2022

Hi @Mit Tolia

just adding to Jack's and Dave's answers, here's another Marketplace app that you might like: JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing and inline-editing all your issue fields. It also allows grouping your issues by any issue field and calculating "sum ups" across these groups - i.e., you can group your issues by their sprint and sum up their story points, like so:

group-by-sprint.gif

It's worth noting that JXL can do much more than that - from quick filtering and sorting, to bulk editing via copy/paste, to support for complex issue hierarchies and nested groupings, etc. More info at https://jxl.app. Disclaimer: I work on JXL :)

Hope this helps,

Best,

Hannes

3 votes
Rahul_RVS_Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 26, 2022

Hi @Mit Tolia 

If you would be interested in a mktplace app for a ready made solution, you can try out

Issue Hierarchy Reports 

And generate insights such as:

- Sum up Time Spent, Org Estimate, Time Rmng, custom number
fields

- View the full Jira Issue Type hierarchy of Linked issues,
Portfolio/Advanced Roadmaps, Epics, and Subtasks up to 10 levels.

- Export your reports to a CSV file

 

(Disclaimer: I work on RVS, the vendor for this app)

Links HIerarchy.PNG

Mit Tolia April 26, 2022

Thanks for your response @Rahul_RVS_Support , but am really after the total of story points for each sprint, rather then at an Epic level.

I do feel the enhanced search app/plugin should suffice but looking for some confirmation from the broader community to validate my hypothesis/understanding

Rahul_RVS_Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 26, 2022

Thanks @Mit Tolia for your reply.

The app does have filter for sprint as well and provides total of story points and time estimates at sprint level.

Do try it out.

1 vote
Dave Rosenlund
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 26, 2022

Hi, @Mit Tolia.

I don't want to contradict @Jack Brickey, but I have seen many situations like yours where Backlog reports don't really fit the bill for leadership. (Jack may have had the opposite experience.) 

Typically, in cases like yours, folks end up opting for more than JQL + Scriptrunner. There is nothing wrong with either of those, but there are a number of Atlassian Marketplace apps that do exactly what you describe.

The most common are BigPicture and Structure for Jira. Both are frequently used in SAFe applications. But there are others. (Note: I work for the company that makes Structure).

Here's a marketplace search that will reveal some of the other possibilities. Hi emboldened the key search criteria for you.

https://marketplace.atlassian.com/search?hosting=cloud&product=jira&query=sum

Hope this helps!

-dave

    

Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 26, 2022

@Dave Rosenlund , please contradict away. The community is all about different perspectives and experiences as it can ultimately help those seeking knowledge. Agree we are likely looking at an addon here.

Like # people like this
0 votes
Marlene Kegel - codefortynine
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
June 14, 2023

Hi,

I am Marlene from codefortynine.

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

With our statistics and pie chart gadgets you can sum up any Jira number field, including story points.

quick-filters-jira-dashboards_story-points-per-sprint.png

After installation simply:

  • Navigate to your Jira Dashboard
  • Add one of the following gadgets: "Quick Issue Statistics", "Quick Two Dimensional Filter Statistics", "Quick Pie Chart"
  • Configure the gadget as any Jira standard gadget
  • Select "Story Points" as value shown

On top of that, you can filter your dashboard dynamically using a Quick Controller gadget. E.g. it would also be possible to filter per sprint. 

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

deepika April 2, 2024

I dont see Story POint as a option to select in the Quick Filters . 

JIRA quick Filter.jpg

Marlene Kegel - codefortynine
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 3, 2024

Welcome to the community, @deepika.

From the screenshot you posted it seems that you are in the config of the native Jira gadget. 

Is the app Quick Filters for Jira Dashboards installed on your instance?

deepika April 3, 2024

ok do i need to install this app separately ? i was thinking its a gadget which come with native JIRA.  I am not sure if i can install something from marketplace or not in my org

Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 3, 2024

That is a third party app that must be purchased 

0 votes
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 26, 2022

Hi @Mit Tolia ,

just wondering why the data presented in the Backlog doesn't work? That is you can click on the ellipses beside the assignee avatar and it delivers a pop up with the estimation details.

85BBCA14-92AB-43F6-8E09-33D1F52C204B.jpeg

Mit Tolia April 26, 2022

Hey @Jack Brickey  - in the backlog view, I believe the estimation details show up only for an active sprint & not for the future sprints.

Also, this is due to the requirement from one of the key stakeholders who is interested in seeing some of this key information (No. of stories in each sprint, total No. of story points for all stories slotted for the current sprint + upcoming sprints, Sprint goals) in one place - either on a Confluence or on a dashboard.

They do not want to swivel chair between multiple views, hence leveraging the backlog view from Jira is not a feasible option & exploring other ways to solve this problem

Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 26, 2022

So the backlog does show this for future sprints. But if that doesn't work you might need to look at an addon app. Ideally you could do something with a gadget on a dashboard but none exist AFAIK.

Like Dave Rosenlund likes this
Dave Rosenlund
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 26, 2022

This is true. But some of the marketplace apps I refer to have Jira gadgets you can use for reports that include other info. (if desired).  I happen to know BigPicture and Structure for Jira do, for example.

Like Jack Brickey likes this

Suggest an answer

Log in or Sign up to answer