Calculating the efforts in story for the dashboard

[Selva Praveen Elango]

I have a dashbaord build, I like to display the total efforts ( sum of original time estimate ) in the dashboard, i have a problem 

when a story have subtask we used to sum up the original time estimates of subtasks and update that as the original time estimate of the story 

we also have story where we dont have sub tasks and we use the original time estimate value on those 

when we sum of the original time estimates if we include the sub task then we get the value doubled , when calculating the total estimate of tasks in done we have a issue 

few of the sub task are completed and the story is not complete so when calulating the time of the done issues we need to include the sub task and not the parent story 

what i need to do is 

if story has sub task then 

sum original estimate of sub task

else 

sum original estimate of the story 

can this be done in JQL in jira 

?

[Bill Sheboy · Accepted solution]

Hi @Selva Praveen Elango 

JQL is an issue search language.  It does not have any aggregation features, such as with a SQL.

To do what you ask you could consider:

• do not include the subtasks' original estimates in your reporting; only report at the story level
• investigate how you sum the original estimate values for the subtasks, and clear the parent value when there are subtask
• create a separate field specifically for reporting which prevents the double-accounting when the values are summed
• investigate Atlassian marketplace addons for reporting / dashboards which allow the type of configuration you are describing

Kind regards,
Bill

[Danut M _StonikByte_]

Hi @Selva Praveen Elango,

This can be easily achieved by using the Advanced Issue Filter Formula gadget offered by our Great Gadgets app. This gadget applies math formulas on the fields of the issues from up-to six filters and displays the result as number(s) on your dashboard.

If you configure the gadget like this:

... you will get the result on your dashboard

 

How this works?

• First, the gadget calculates the sum of Original estimate for all the issues (which includes stories and sub-tasks) and stores it in variable "a"
• Then, the gadget calculates the sum of Original estimate for all sub-tasks and stores it in variable "b"
• Then, it displays the result of formula "(a-b)/3600". Division to 3600 is necessary to transform from seconds in hours (because Jira stores the time estimate fields in seconds)

You can start with a 1-moth free trial. If you have any questions, please contact support@stonikbyte.com.

See more about this gadget (and the many others offered by the same app) in these articles:  

• https://community.atlassian.com/t5/App-Central/8-gadgets-for-a-powerful-Scrum-dashboard-in-Jira/ba-p/1683063
• https://community.atlassian.com/t5/App-Central/How-to-track-scaled-agile-SAFe-projects-in-Jira-with-Great/ba-p/1929694

Hope this helps.

Danut.