Creating a filter of stories with a column showing when moved into that status

[Jennifer Arkell]

Hi, I'm struggling to work out how to do this (but feel it must be possible). 

I want to create a query that can show all the stories in progress, with a date they were moved to a particular status (currently, "in progress"), ordered by date. 

I can't work out how I can configure a column in my query to demonstrate this? 

[Valeriia_Havrylenko_SaaSJet]

Hello @Jennifer Arkell 

I will be glad if my article about JQL helps you. 

As an alternative , I guess you can try Time in Status for Jira Cloud (developed by my SaaSJet team) with 7 types of status time reports. 

For example The Time in status report shows how long an issue has been spent in each status.

Add-on has a 30-day free trial version and free up to 10 users. 
Please, let me know if you have any questions

Hope it helps 😌
Valeriia

[Mark Segall]

Hi @Jennifer Arkell and welcome to the community!

While you can query when an issue transitioned to In Progress, there is no "field" that captures this.  So, you won't be able to do this without some extra work.

The most common workaround is to create a custom field to track this information and use automation to update the field.  It would look something like this (I'm using a custom date field called Last Transitioned for my example):

• TRIGGER: Issue Transitioned
• ACTION: Edit Issue (Last Transitioned)
  • {{now}}

[Jennifer Arkell]

thanks, unfortunately I don't have the Administrator rights so unlikely to be a goer for me. But helpful to have the mechanics explained to me. 

[Mark Segall]

You could check with your Jira Admin to see if they already have another date field that you can use for this purpose.  Ideally something with naming that is close to what you're trying to capture.

[Madhu_RVS]

Hi @Jennifer Arkell 

Welcome to the community !!

As this is not achievable OOTB, if you are open for a mktplace add-on, you can try our addon:

Time in Status Reports 

The add-on provides the status transition dates and there are 20+ reports in the app which help to meet a wide range of use cases.

Do try it out.

Disclaimer : I work for RVS, the vendor for this app

[Emre Toptancı _OBSS_]

Hello @Jennifer Arkell

Jira does not provide this functionality out-of-the-box. JQL queries can "filter" issues but they can't make calculations and they do not show transition dates as columns in their result set. The data you need is available in each issue's history but Jira doesn't show this as a ready-to-go report.

You can use the custom field approach mentioned in other replies.  The downsides of this approach are:

• You need to create a separate custom field for each transition date you want to track.
• You need to configure these post functions to all the transitions in your workflows.
• The custom field solution works only after this configuration is made. Doesn't work for past issues.

I suggest you use a marketplace app to get ready-to-go reports.

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

Time in Status has duration-based and date-based reports.

Almost all of the time, the reason to get the dates is to see the duration in a particular field.

Time in Status mainly allows you to see how much time each issue spent on each status or each assignee. 

   

You can combine the time for multiple statuses to get metrics like Issue Age, Cycle Time, Lead Time, Resolution Time etc. 

You can also use the date-based reports to see the transition dates from/to a status

For all numeric report types, you can calculate averages and sums of those durations grouped by the issue fields you select. For example total in-progress time per customer or average resolution time per sprint, week, month, issuetype, request type, etc. The ability to group by parts of dates (year, month, week, day, hour) or sprints is particularly useful here since it allows you to compare different time periods or see the trend. 

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. It supports both Company Managed and Team Managed projects for Jira Cloud.

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. You can also get file exports and REST API access.

  

Timepiece - Time in Status for Jira

EmreT

[Bill Sheboy]

Hi @Jennifer Arkell -- Welcome to the Atlassian Community!

As others have noted, there is nothing built-in to do this when you have multiple in-progress status values.

Let's assume you only have one or you only care about issues in-progress regardless of status.  That is possible with the statusCategoryChangedDate field.  For example:

project = yourProjectName 
 AND statusCategory = "In Progress"
 ORDER BY statusCategoryChangedDate DESC

If you have more status values and need that level of measure detail, please look at the answers using automation rules/custom fields or marketplace addons.

Kind regards,
Bill

[Mehmet A _Bloompeak_]

Hi @Jennifer Arkell

Welcome to Atlassian Community!

If you prefer to use a marketplace app, you can try Status Time Reports app developed by our team. It mainly provides reports and gadgets based on how much time passed in each status.

Here is the online demo link, you can see it in action and try without installing the app. For your case, please see Status Count And Entry Dates report. Entry date(see In Development, Ready for Testing, In Testing, In Development columns.) is status transition date and status count(see #In Development, #Ready for Testing, #In Testing, #In Development columns) is how many times an issue is entered to this status. For further details, you have a look at Status Time Reports How to Videos.

• This app has a dynamic status grouping feature so that you can generate various valuable reports as time in status, time in assignee, status entry dates and status counts, cycle time and lead time, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month).

• You can search issues by Project, Issue Type, Status, Assignee, Issue Creation/Resolution Date(and any other Date field) and JQL Query.
• Status durations are calculated according to the working calendar you define. 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.
• You can set different duration formats.
• You can export reports in CSV file format and open them in MS Excel.
• You can also add this app as a gadget to your Jira dashboards and reach “Status Time” from Issue Detail page.
• You can enable/disable access to Status Time reports&gadgets and Issue Detail page per project, users, groups or project role.

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

Hope it helps.