JQL to calculate user wise original estimate value

Question

Dear Experts,

I need your support to create an JQL to setup an dashboard identify scrum team member wise original estimate allocation. This to mainly identify the number of hours allocated for particular team member during the sprint planning ( sprint not started yet )

Example : Am currently during sprint planning named as 'alpha' and assigned  following 5 tasks to 2 team members

User Story 1

User Story 2

Name: Sub task 1 | Assignee : John | Original Estimate : 7h 
Name: Sub task 2 | Assignee : Albert  | Original Estimate : 5h

Query should return following results for sprint alpha 
John : 15h ( Original estimation Sum of sub task 2 and Sub task 1 ) 
Albert : 20h ( Original estimation Sum of sub task 1, Sub task 3 and sub task 2  )

Results should be available for all the team resources who bee allocated tasks within the sprint. the main objective of this activity is to identify the remaining effort for each resource by considering 80h is the available capacity for a single resource during 2 week sprint.

Hannes Obweger - JXL for Jira · Answer

Hi @Chamath Gunasekara

welcome to the community!

If you're open to solutions from the Atlassian Marketplace, I think you might like the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a range of advanced features, including support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

With these, you can build a view like e.g. this in just a couple of clicks:

This is really just one of a virtually endless number of possible views and reports; you can also view and group by any other issue fields, configure different sum-up styles, etc. etc.

Any questions just let me know,

Best,

Hannes

Amay Purohit_RVS · Answer

Hi @Chamath Gunasekara

Welcome to the community !!

f you are fine to try out a mktplace app, you can have a look at the one we have developed for the same use case.

Capacity Planner

The app allows to store the capacity for individual users and save as templates. which can be mapped to a project / fix version to track the allocation and remaining time per user. Do try it out.

The app also shows detailed tasks assigned to each resource and the sum of time spent vs original estimates.

Disclaimer : I am part of the team which developed this app

Marlene Kegel - codefortynine · Answer

Welcome to the community, @Chamath Gunasekara.

I am Marlene, product manager of Quick Filters for Jira Dashboards.

Maybe our app can help you solve your use case. With Quick Filters for Jira Dashboards you can sum up any Jira number field and filter the Quick gadgets dynamically.

You could work with the Quick Issue Statistics gadget, select as field "assignee" and show the statistic for the number field "Original Estimate".

On top of that you could filter the gadgets per sprint using the Quick Controller gadget.

You can test Quick Filters for Jira Dashboards without installation on our demo dashboards.