Query to know the total time spent on all tickets in a particular epic per week or per sprint

[Sherwin Soriano]

I would like to seek assistance on a query on how I can see my logged hours in a project for this week only. 

There is a related article before which is 

https://community.atlassian.com/t5/Jira-questions/JQL-Query-to-retrieve-User-Stories-with-Time-spent-on-them-or/qaq-p/2355935#M1079781 but this is more of specific on how I can segregate the time spent from last week, to the time spent this week, for reporting purposes.

Is this possible?

[Trudy Claspill]

Hello @Sherwin Soriano 

In the post you referenced the original author has access to a third party app that extends JQL functionality. Do you have access to such an app in your Jira instance?

That post is also asking about getting issues where time was logged against a parent issue or it's subtasks. Is that relevant to your requirement?

Lastly that post is about getting the issues, not about reporting on the time itself. If you are trying to get a report of time logged during a date range, that is really an entirely different requirement than the original post.

Can you please state the entirety of your requirements?

Jira's native filter/JQL capabilities are not sufficient to meet your requirement. It does not support filtering for issues based on when time log entries were made. Filter results are also not capable of providing any sort of sum of a subset of the time log entries for individual issues.

There are third party apps capable of providing reports of time logged based on when that time was logged. One such app is 

https://marketplace.atlassian.com/apps/1219004/worklogs-time-tracking-and-time-reports-lite-time-tracker?hosting=cloud&tab=overview

[Sherwin Soriano]

@Trudy Claspill 

Our management would like to create a report for all developers who logged their time spent every week so they can check if this number, matches the number they logged on another time tracking tool.

This report is on a weekly basis for every developer we have.

[Sherwin Soriano]

In the post you referenced the original author has access to a third party app that extends JQL functionality. Do you have access to such an app in your Jira instance? - No we don't have this app in our instance

[Trudy Claspill]

Hello @Sherwin Soriano 

Jira does not provide a capability to get the time logged per person within a date/time range. You will have to acquire a third party app to generate such a report. A few different ones have been mentioned here in my response and the responses from other community members.

If your company is not willing to consider a third party app then you would need to develop your own custom solution leveraging the Jira REST API to get and manipulate the data you want.

[Gökçe Karaduman-The Starware]

Hi @Sherwin Soriano ,

To generate the detailed report you mentioned, I recommend using a third-party application available on the Atlassian Marketplace. One such tool is WorklogPRO. With WorklogPRO’s timesheet feature, you can group worklogs by specific weeks, users, projects, and other fields. After creating a JQL in Jira for the issues you want to display, you can select this filter in the "Filters" section of the report dialog to generate the report. Additionally, for each grouping you select, you can analyze the total worklog amounts through a column that shows the sum of worklogs.

Disclaimer:I work for the vendor who developed this application.

[Rahul_RVS]

Hi @Sherwin Soriano 

For detailed time tracking reports if you are willing to consider a mktplace app, take a look at our add-on.

Worklogs Time Tracking & Timesheets

With this add-on, you can easily view all time entries for multiple issues day wise grouped by in a single view. Main features of the app -

• Create time reports in real time by grouping & categorizing data with aggregation
• Prepare your reports based on Project/Sprint/Issue Type/Assignees and various other filters
• Group your data to build more meaningful reports
• CSV Export
• Dashboard gadget
• Enter Time Spent for multiple issues from Timesheet screen

(Disclaimer: I am part of the app development team)

[Lisa Teves _Appfire_]

Hi @Sherwin Soriano, As mentioned in some other responses, the best way to see the total time spent on all tickets in an epic per week or per sprint is likely through a third-party app, as it's not possible with Jira alone. 

I recommend looking into 7pace Timetracker for Jira, which allows you to easily track time spent on all issues within a specific timeframe (plus a lot more!). 7pace Timetracker makes it incredibly easy to track time on an issue or use calendar views or a timesheet - flexible based on the individual's preferences. It's built right into your existing workflows and looks and feels just like Jira, so time is tracked more consistently and accurately, leading to better data and insights. 

I've included a few examples of reports that can be created in seconds, showing how you can group data based on project, person, or epic. Please let me know if you'd like any more information or if you would like to see the app live in a demo. 

Worklogs grouped by Project, Person, and Epic:

Drill down into details as needed:

[Michał Kowalewski_SolDevelo]

Hi @Sherwin Soriano as others mentioned you need to use third party app to generate the report you're looking for.

If you are looking for a tool that is easy to use and yet provides the features you need, then you should definitely check out Worklogs - Time Tracking and Time Reports, app already mentioned here.

In the app you filter time logs to display them for your projects, users etc. for the particular week, month, year etc.

There's also a possibility to display your JQL in the report. For example, I used JQL filter to display only bug tickets which are to do and in progress.

If you have any questions regarding the app feel free to contact us here, or book a demo session here.

Disclaimer: I work for the company that developed the above mentioned app.

Kind regards,
Michał
SolDevelo Team 

[Alexey Pavlenko _App Developer_]

Hi @Sherwin Soriano ,

JQL you are looking for should look something like

timespent is not empty AND sprint = %YOUR_SPRINT%

 after running it, you need to export it to Excel and calculate the sum there.

If you are looking for an automated solution, you can try the one I have developed - Multi-team Scrum Metrics & Retrospective. Thanks to it, you can get this:

Or can do a drill-down and get this view:

You can see on one bar chart several teams/boards and several sprints at one time as you can see.

Best regards,
Alexey

[Trudy Claspill]

@Alexey Pavlenko _App Developer_ 

The Time Spent field is the total time logged against an issue, regardless of when that time was logged.

Your query would return all issues in the current sprint where there was time logged on any date, not just time logged during the sprint (or any other date/time range).

[Alexey Pavlenko _App Developer_]

@try ,

Good point.

@Sherwin Soriano ,

It's important to note that the solution I proposed above works only for those issues that weren't rolled over from a previous sprint. When you have rolled over issues, you need to manually substract their timespent not related to the target sprint. To find these issues, you can use Sprint Report, for example.

Best regards,
Alexey

[Dan -minware-]

@Sherwin Soriano - minware offers one of the 3rd party apps others in the thread have mentioned. We have an AI data model that estimates how much time went into each commit without the use of timekeeping.

The data model can be retroactively applied to any of your projects and broken down by any other field from git or jira - here's a screenshot from our live demo that shows an example:

We offer a free trial if you want to check it out!