It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to access total time logged per release outside of Jira

Is it possible to access the result of a Time Tracking Report over the Jira API?  In particular I need the total time spent value for a Fix Version.

I am already using the API to bring total time on individual issues into my accounting system but time per release is really what I need, outside of Jira.

If this isn't possible, what other options do I have please?

1 answer

1 accepted

0 votes
Answer accepted

Hi Chris,

The easiest solution is on the system front end where you can generate a "excel view" export of the "Time Tracking Report" by going to the Project > Reports > Time Tracking Report, input the desired version to generate the report and select "Excel View" to download the data which can be opened in a spreadsheet application.  Additional details on the report can be seen here:

One caveat on this however is that the export is a html encoded sheet saved as an XLS so tools like OpenOffice will open the sheet without issue with the default settings but in Excel you will run into the same scenario described here: "JIRA Excel export of issues no longer opens correctly" and require temporarily disabling the security property to open a document from a trusted source on Excel's side.

To pull the data from the API it gets a bit more complicated as the Worklog is stored on the issue level at the endpoint GET /rest/api/3/issue/{issueIdOrKey}/worklog and the sum is generated by the report so its not a directly queryable data point meaning it must be retrieved and calculated manually from the avaliable data set. 

There is a really good exe on how to do this on a per user basis from the API with example scripts linked via github repos in the following community post, which could be easily modified to exclude the user specific worklog and focus on the full release details, and would be a really good starting point to pull and collate the data set:

Another really good approach here is that you can also look at our free google sheets add-on "Jira Cloud for Sheets" that allows you to import data directly to a google sheet via an =jira() command then use the spreadsheet formulas to calculate and manipulate the data values as needed for your report.

If you go this route to collect the data you need to identify all issues that meet the criteria for the version and then run the comparisons and summation of the worklog across the issues in the result set.  The time tracking fields to look at are going to be Original Estimate, Time Spent and Remaining Estimate,  as well as the Aggregated versions of those fields signified with an "Σ" character exe Σ Original Estimate, Σ Remaining Estimate, Σ Time Spent toy could do something like the following to pull in all the time tracking fields:

=jira("project= EXE_Project AND fixversion in (1.0) order by created DESC","issuekey,issuetype,originalEstimate,remainingEstimate,Σ Original Estimate,Σ Remaining Estimate,time spent,Σ Time Spent",0,100)

Regards,
Earl

Earl, thank you so much for your very detailed response.

Like Earl McCutcheon likes this

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

303 views 1 3
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you