Automated reports which extracts blocked and waiting status along with time spent.

[yashwanth]

Hi,

one of our User ask is..

"we would like to have automated reports which extracts blocked and waiting status issues and shows duration spent in the status"

Is there any way we can achieve this without the help of Add -on.

Thank you,

Yash.

[Emre Toptancı _OBSS_]

Hello @yashwanth

Jira does not give this data OOTB and certainly does not give it in an automated way but the data needed to calculate this report is available in every Jira issue's history.

You can try measuring this using custom fields and workflow functions. There are several examples of this in the community. The first downside of this solution is, it is hard to build and maintain. You have to repeat this for every single measurement you need. And also it works only for future changes, does not work on past issues.

Alternatively, you can try getting history data from Jira DB (since you are on server) or REST API and do the calculations yourself. The data you'll get will fundamentally be the same data you see in the History tab of each issue. You can calculate the report by processing this data. This requires some serious custom programming.

If you have Jira Software, you can use the Control Chart of Kanban boards to get an overall picture. Control Chart does not show individual issues but you can create a Kanban board just for reporting. Define the issues you are interested in as a Quick Filter. Define your board columns based on the statuses that you want to see the durations for. Then use the Control Chart to see if it helps. Control Chart doesn't show individual issues and has very little flexibility in terms of reporting but it should work well to give you an overall picture.

Finally (I know you said you want to do this without an app but) you can use one of the marketplace apps for a ready solution.

Our team at OBSS built Timepiece - Time in Status for Jira app for this exact need. It is available for Jira Server, Cloud, and Data Center.

Time in Status allows you to see how much time each issue spent on each status. You can also pick individual statuses into Consolidated Columns to see metrics like Resolution Time, Ticket Age, Cycle Time, or Lead Time.

You can calculate averages and sums of those durations grouped by the issue fields you select. For example average resolution time per week, month, issuetype etc.

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts.

  

For automation, you can use Time in Status REST API to get the calculated reports programmatically. You can also use our sample Excel or Google Sheets documents to automate getting data directly into your spreadsheets.

Using Time in Status you can:

• See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
• Calculate averages and sums of those durations grouped by issue fields you select. (For example, see average InProgress time per project and per issue type.)
• Export your data as XLS, XLSX, or CSV.
• Access data via REST API. (for integrations)
• Visualize data with various chart types.
• See Time in Status reports on Jira Dashboard gadgets

Timepiece - Time in Status for Jira

EmreT

[Mehmet A _Bloompeak_]

Hi @yashwanth,  welcome to the Atlassian Community!

Unfortunately, what you are looking for is not possible with the built-in functionalities of JQL. Because JQL can not make calculations and you need waiting time to be calculated.

For this exact need we developed Status Time Jira app. It provides reports on how much time passed in each status. By grouping "blocked" and "waiting" statuses you can get total waiting time.

Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days. It has various other reports like assignee time, status entry dates, average/sum reports by any field(eg. average in progress time by project, average cycle time by issue creation month). And all these are available as gadgets on the dashboard too. Here is the online demo link, you can see it in action and try without installing the app.

If you are looking for a free solution, you can try the limited version Status Time Free. Hope it helps.

[RVS_Support]

Hi @yashwanth 

As suggested, you might want to try out an add-on to meet your requirements. You can have a look at our plugin

Agile Tools : Epic Tree & Time in Status 

The add-on provides the time in each status for the entire lifecycle of the issue. You can also combine your statuses to define your Resolution time and also extract the transitions history of the issues. Along with various Issue stats reports, you get additional features like Epic Hierarchy, Links Hierarchy & Worklogs Report to track the project's progress. The main features of the app are as below

• More than 8 types of Time in Status Reports. Excel Export available for all status reports.
  
  • Time in Status
  • Time with Assignee
  • Time in Status with Assignee
  • Time with Assignee per Status
  • Status/Assignee Count
  • Multiple Transition Reports
  • Avg Time reports
• Epic Hierarchy / Sum Up (Standard Jira Hierarchy Epic -> Story -> SubTask)
• Link Hierarchy / Sum Up (Hierarchy based on your issue link, upto 10 level deep)
• Worklogs Report
• Timesheet Entry screen

[Jack Brickey]

OOTB there isn't a good solution for time in status. You might consider an addon for this. arguably you could create custom fields for entering and exiting statuses coupled with automation but that is less than ideal.

[Bill Sheboy]

Hi @yashwanth -- Welcome to the Atlassian Community!

Would you please clarify the problem you are trying to solve by having the time in status with these checks?  Ignoring that, you may be able to solve this by creating a saved filter and subscribing to it...depending upon your definitions of "blocked" and "waiting".

For example, to find "blocked" issues marked by the flag of impediment, the JQL would be:

project = myProject AND "Flagged[Checkboxes]" = Impediment ORDER BY Key

And for issues that have not changed status in a 48 hours, you could use:

project = myProject AND NOT status CHANGED AFTER -48h ORDER BY Key

As Jack notes, to get more clarity on time-in-status, you would need a marketplace addon or to add/update a custom field to track the time, such as with an automation rule.

Kind regards,
Bill

[yashwanth]

Hello Bill,

Thanks for the information. I’ll check and let you know the Outcome.

Thank you,

Yashwanth.