How can I query on number of times in a status?

Lisa Zentz
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
March 7, 2018

I'd like to find the number of times issues were moved to a particular status type that would result in a count - e.g. 3 times in status x.

 

How do I structure that?

 

Thanks!

8 answers

4 votes
Imran
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 21, 2019

Hi,

I want to get the status of an issue without opening the Jita site, directly into excel. Can someone share the van for it. Many thanks 

Mark Kern [SaaSJet] June 24, 2019

Hello @Imran 

The add-on Time in Status for Jira Cloud by SaaSJet can meet your expectations.

It generates such reports (all of them are available for export):

- Time in status - automatically determines for how long your issues had been staying in each status

- Status Count - shows how many times an issue has been in each status

- Transition Count - shows how many times an issue went from status to status

Also, this app will show you: Average time in status, Assignee time, Status Entrance Date and Time in Status per Date.

All data from reports you can view as tables or as charts.

 

Regards

Like # people like this
4 votes
Alexey Matveev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 7, 2018

You can not do it in Jira interface. But you could use Jira Rest Api to receive the history of an issue and find the number of times the status was changed to the required status in Excel or other similair application. You would need to use GET /rest/api/2/issue/{issueIdOrKey}  with changelog option. You can find more info here:

https://developer.atlassian.com/cloud/jira/platform/rest/#api-api-2-issue-issueIdOrKey-get

Darius September 19, 2019

Hi Alex,

The rest api produces a result which is similar to below.

How can align that to excel view.

 

Filter for status.PNG

3 votes
Meg Holbrook
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 7, 2018

There might be an easier way than mine, but here is how I do it: (I also employ AIO reports addon for reporting)

Filter: project = EncompassHelpDesk and status was "Waiting for support" during ("2018/02/01","2018/02/28")

Once results are exported to excel, I create a pivot table and group by key. 

Result is a table that shows how many times each issue was in (my case) waiting for support. 

 

1.png2.png

Kenny Nguyen
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
October 3, 2018

Hi @Meg Holbrook, I ran the above query and got the results, but each issue only appeared once, so I can't do the count, even though I'm very sure that they had moved to the status several times. I don't have AIO report. Thanks.

Like David Leal likes this
Meg Holbrook
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 3, 2018

Hey @Kenny Nguyen - did you expand your date range enough to capture all the possible times it would have transitioned into that status?

skylinek
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
November 13, 2018

Hi, 

is it still working? because im not able to do it , i have same problem as Kenny. I exported but  all tasks are mentioned only once. i dont have AIO reports ... is that the problem ? Thank you 

 

Skyline

Like # people like this
David Leal
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 14, 2019

I have the same problem, I get only one entry per issue key. When I exported the excel file I export all fields, I get some dates, such as: Created, Updated, Las Viewed and Original Entry Date, but I don't see a way to get the historic information each time a given status was reached in the line I have exported.

Like Eran Flom likes this
alaasaqer
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
June 16, 2020

The problem that the query (excel file) will only capture the latest status of the issue, not all statuses that the issue was in! 

I am not sure how the JQL worked for @Meg Holbrook  Please Advice. 

Like Eran Flom likes this
Meg Holbrook
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 16, 2020

The important part is in bold, which should pull the same issue multiple times.

 

project in (_________) AND issuetype = "Service Request" and status was "Waiting for support" during (startOfMonth(-1), endOfMonth(-1))

Eran Flom March 15, 2021

I am sorry - but even with bold part - it only pull each issue once.

Is there a solution to this? Anyone manage to make this work?

2 votes
Mehmet A _Bloompeak_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 29, 2020

For cloud, you can use Status Time add-on. It generates status count report in addition to status time report.

If you are looking for a free version, you can try Status Time Free.
StatusCount.gif

Deleted user November 8, 2022

Using this app but that slider is not available in our Cloud instance.

Mehmet A _Bloompeak_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
November 9, 2022

Hi @[deleted] ,

We have made some improvements on the UI. Now the toggle is under "Report Type" as in the below screenshot.

show Jira status entry count.png

2 votes
Lime Trees March 8, 2018

Hi,

you can have a look at our free add-on Status History which will show you history of statuses for each issue - more details you can find here.

If you need to generate report or analyze data regarding issue statuses, you can also try Status History PRO, which has reports (especially Changing Status History Report in your case) and  customizable viewer.

Regards,

Lime Trees Support Team

David Leal
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 16, 2019

Thanks it a great solution, but is paid version and we already pay for having Jira. This is something we may expect having out of the box in Jira, because if we can design a workflow we would like to know when you reach a particular status and how many time is reached. 

Like # people like this
1 vote
Emre Toptancı _OBSS_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
May 21, 2019

You can use Time in Status by OBSS, which creates reports showing how many times each status or transition was used. It also shows how much time each issue spent on each status, assignee or group. Can export data as XLS or CSV.

https://marketplace.atlassian.com/apps/1211756/time-in-status?hosting=server&tab=overview

1 vote
Arun_Thundyill_Saseendran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 7, 2018

You can make use of the Easy BI plugin to do these kinds of aggregations.

https://marketplace.atlassian.com/plugins/com.eazybi.jira.plugins.eazybi-jira/server/overview

Meg Holbrook
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 8, 2018

Am I the only one who doesn't consider eazybi to be incredibly user-friendly?

Maybe I'm reporting-challenged.

Arun_Thundyill_Saseendran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 9, 2018

I agree that eazybi is not incredibly user-friendly. However, it does help us do a lot of reporting right within JIRA. In the earlier case, we had to do all of the complex reporting by syncing the issues with a database and then use Tableau to do the complex reports.

0 votes
Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
May 26, 2024

Hi,

If you're open to solutions from the Atlassian Marketplace, this is now easily possible using the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren’t natively available, including number of times in [status], number of transitions between [status] and [status], and many, many more.

This is how it looks in action:

number-of-status-changes.gif

As you can see above, you can easily sort and filter by your history columns; you can also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events