How to access total time logged per release outside of Jira

[Chris Taylor]

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?

[Earl McCutcheon · Accepted solution]

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:

• https://confluence.atlassian.com/jirasoftwarecloud/reporting-764478415.html

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:

• https://community.atlassian.com/t5/Jira-questions/How-can-I-generate-a-report-of-time-logged-per-person-and-not/qaq-p/3093

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

[Chris Taylor]

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