Sum of original estimate in issues by filter

Janne Enberg September 18, 2012

I'd like to get a total sum of original estimate for issues (by filter condition) in a project that would do something like this:

SELECT SUM(originalEstimate) FROM ... WHERE project="Project Name" AND status IN("Resolved", "Closed");

Is something like this in any way possible from Jira? I managed to write a real SQL query to my MySQL database that gives me the result, but it's a bit clumsy compared to doing it from Jira.

17 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

19 votes
Answer accepted
Tomáš Vrabec August 1, 2017

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.

Gabi Nir August 13, 2017

Hi,

 

When I create the pie chart like you said they only relevant option is the assignees but then it sums up the number of issues and not (ofcourse remaining).

Looking at the rest of options to choose grouping by the results, there is no effort estimation.

Can you advise?

Thanks!

Tomáš Vrabec August 14, 2017

Attaching screen of my settings.

Snímek obrazovky 2017-08-14 v 14.06.30.png

Like # people like this
Gabi Nir August 14, 2017

It solved my problem!

Thank you!!! :-)

SvenM November 15, 2017

Perfect solution. Thank you very much, Tomáš!

Philip Dang: Insurance-VN March 20, 2018

Thank you so much. That's the great solution.

Balakrishna M July 16, 2018

Great , Its a work around , but still it met my requirements

Николай Чернов October 27, 2018

Thanks! 

Sébastien Sacard July 29, 2019

Thank you ! 

Cormac Smith September 11, 2019

Just a note that "Current Estimate" reports the stat referred to in the JIRA issue work log as "Remaining Estimate." Fortunately, that is exactly what I needed. Thanks to Tomas!

Ahsan November 1, 2019

This exactly what I need. I am using Cloud version of Jira. For some reason I am not seeing the "Workload Pie Chart" Gadget. Do you know how I can access it?

Michele.Primmer January 3, 2020

I can't see Workload Pie Chart either, we are on the server version. Please advise? Is this yet another thing we have to pay for?

 

thanks

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 3, 2020

The workload pie chart is available in Cloud and Server.

Michele.Primmer January 3, 2020

When I go to ADD GADGET >> ALL>>SEARCH there is only one pie chart available and it's not the Workload Pie chart...it's only the chart with the one Statistic Type field.

 

thanks for any insight on where to find it

Like Jo Beck likes this
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 3, 2020

Ah, found it. 

It's always installed on Cloud by default, but Server installs need to add the "Jira Charting plugin" - this is one of 5 I always install so I forget exactly what charts it provides.

Like # people like this
Michele.Primmer January 3, 2020

ok, will look for that plug in - thanks!

10 votes
Ersen Kavak June 12, 2016

When summing up hours is so central to Agile workflow, not having this in default JIRA does not make sense. The plugin is good, but extra payment feels weird.

5 votes
David Skreiner January 8, 2015

If you have ScriptRunner, this JQL query should show you the overall remaining workload for the current user:

 

project = PROJECT and assignee = currentuser() and issueFunction in aggregateExpression("Total Estimate for all Issues", "originalEstimate.sum()", "Remaining work", "remainingEstimate.sum()")

The documentation is here:

https://jamieechlin.atlassian.net/wiki/display/GRV/Scripted+JQL+Functions#ScriptedJQLFunctions-AggregateExpressions



Linus Lindroth May 25, 2015

Very nice!

Abyakta Lenka September 18, 2015

Very Nice , But How do you display this in confluence page or any where else .

3 votes
Christian Czaia _Decadis AG_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 13, 2013

Hey,

we recently released a plugin that calculates totals... You might want to check it out:

https://marketplace.atlassian.com/plugins/com.atlassian.cps.sumUp

Cheers

Christian

Angelos October 7, 2014

It would have been great if SumUp included "Original Time Estimate" in the fields it can sum up :)

Like Mike Rowe likes this
Karl Gram August 5, 2015

Great app for a great price. If only this was built in to JIRA already... Thank you for existing, sumUp.

2 votes
drabiger August 6, 2018

I just added a Chrome extension to the Chrome webstore that shows the summary of remaining estimates in Jira scrum boards, if you show the remaining estimates per issue: https://chrome.google.com/webstore/detail/estimations-sum-for-jira/mmbocjepfajlidapdnikdjebbdfjgbki

2 votes
F December 25, 2013

Would also love to have a SUM function in JQL.

2 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
September 18, 2012

JQL is not SQL. It is exclusively designed for finding issues, it has nothing to say about the output (the line blurs slightly with the "order by" but that's actually still separate from what JQL does)

Think of it as two separate parts. You do the search in JQL, and that gives you a list of matching issues. That's all it does.

If you want to see that output, you have to hand that filter to a display mechanism. Jira has loads of these - broadly the issue navigator, gadgets, reports. The display mechanism then reads and formats the data on the issues in whatever way you want.

So, there's no way to do this in a JQL query, because JQL tells Jira nothing about the output. You need to find a reporting mechanism. Have a look at the issue navigator and the "sum" fields (prefixed with sigmas in the "add column" drop down list). The project reports might tell you something, as well as some gadgets. I suspect none of them will do quite what you want, as they're all generalised, and you may need to write something.

Janne Enberg September 18, 2012

Yes, I understand JQL != SQL, and knew it is not possible to do this with JQL, which is why I said "in any way possible from Jira" .. I might take a look in the Jira plugin database

boardtc
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 10, 2012

Any luck finding a plugin to do this? It should be basic functionality on the new v6 rapidboard.

1 vote
Olesia Hirnyk August 26, 2013

The same problem...

I need to receive sum of Remeining time for all tasks assigned to currentUser()

:(

0 votes
Daniel Morgenstern June 20, 2019

I ran into this problem a lot of times, so I decided to build a small solution that doesn't need any extensions.

I hosted the code on GitHub: https://github.com/dmstern/jira-hacks

Feel free to star, fork, contribute, share. :)

Daniel Morgenstern December 6, 2019

Sorry, repost wasn't intended. I just wanted to edit my answer and then it suddenly got auto-saved.

0 votes
Daniel Morgenstern June 20, 2019

I ran into this problem a lot of times and decided to build a simple solution that works without any extensions and hosted the code on GitHub:

https://github.com/dmstern/jira-hacks

Feel free to fork, star, contribute, share. :)

*Edit: I deleted the manual and the code because the jira forum broke it. You can get the current maintained version on GitHub*

If you encounter any bugs, please file an issue on GitHub

Pablo Osorio da Silva August 26, 2019

Hi.... I was with high expectation, but unfortunately it did not work for me. There seems to be this error after pressing the bookmark: "Estimated remaining effort for the displayed issues: NaN PT.".

Any hint here? Tks

Pablo Osorio da Silva August 26, 2019

I also tried the Calculate Cost script, and this did run, although after asking me to set €/h it respond: "Estimatet cost for displayed issues: NaN €."

Daniel Morgenstern November 12, 2019

@Pablo Osorio da Silva 

Do the issues in your filter/search have remaining estimations in the time format (not story points)?

Daniel Morgenstern December 6, 2019

@Pablo Osorio da Silvacould you please file an issue on GitHub: https://github.com/dmstern/jira-hacks/issues

 

I would like to investigate and help you with that issue.

0 votes
Korayem February 17, 2019

Not the fastest solution, but I export JQL results to CSV, open Excel and do whatever I want from there

 

Note that time columns (original estimate, remaining, logged) are shown in seconds, so divide their values by 3600 to convert to hours

0 votes
Mukesh Singhal July 17, 2018

I want to see story points loaded for each sprint for planning purpose. The gadget Workload Pie Chart do not show Story Points or any other number filed or custom fields that can be summed up.

Any thoughts

0 votes
christopher_griggs January 31, 2018

yeah, this is really annoying, and something you would expect by default. Rally has a nice way to total while planning. Also, the solution suggested, I don't have any sort of gadget called "Workload Pie Chart" - it's not 'built in'. So... 

Melody White June 25, 2018

When you open a Dashboard and click on Gadgets in the top right hand corner,  it does not load all the gadgets immediately. You have to click Load more gadgets and then scroll to the bottom of the list.  It should be the last option. 

0 votes
philippos December 1, 2016

Hello,

Is there any update on this? A plug in which sums the original/actual estimates?

0 votes
Antonio Duarte November 18, 2015

I have the same problem. How can I add these values and keep them "available" to make some calculations that I want to put in some reports?

Besides, the info I want to add is the total time spent by the issue, since its beginning until its closure.

As I have seen so far, the sumUp add-on adds only numeric fields.

Any help, please?

0 votes
steven.schafer@nike.com May 22, 2014

I can see this on the main dashboard as a option for adding a field, yet I can't write a filter showing the Sum of values. I'd love to say where sum(timeoriginalestimate) is 0, highlight these issues in my filter. I'm using with sub-tasks where the hours are actually estimated.

Any thoughts on how to fix would be ideal!

Gerard Cattin July 22, 2016

This is a high item for me as well. I use subtasks, and being able to tell if subtasks exist or if they have any aggregate time allocation is important to understand work in sprint not yet accounted for.

philippos December 1, 2016

Any news on this? It is unbelievable that a plug-in to sum estimates per user in a release does not exist!

0 votes
boardtc
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 11, 2012

It would be nice if this was default or a plugin was added. The way I do this right now is to add the orig estimate column in the issue navigator and move it over near the start. That way a tot up can be done easily enough, e.g:

Russ April 16, 2013

I would also like to find an answer to this. Currently I'm being forced to export into excel to do this..... and I HATE spreadsheets.

A standard "Provide Column totals" tickbox on the display gadget would be perfect fo this.

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question