Days in Status - JQL query Support for building a Report/Dashboard

Rahul Jith

Hi Bill Sheboy, @Bill Sheboy 

Good day!

First of all, thank you for your outstanding solution which I had found really helpful and I was able to replicate in my project. Many thanks for that. 

I am here now based on a related item which you had already assisted many threads in the past. My company is not authorized nor allowed to use any add ons nor additional apps from market place. So I am looking for a best solution for  my query.

 

To summarize, I have 2 requirements. Same requirements in 2 formats I can say:

 

1. I wanted to pull a report or dashboard to showcase the number of the days an issue sitting in some particular status.

2. I wanted to notify certain stakeholders if an issue sits in certain status more than 15 days(I have attached the JQL query which I had used based on your responses on similar topics in other threads. The JQL results is coming with a wrong output and I am seeing issues which are created same day, yesterday and less than 15 days also)

Can you please review and suggest me a better JQL query which can fulfill this.

 

 

 

 

Bill Sheboy

Hi @Rahul Jith 

Starting with the JQL you are showing, please note queries evaluate AND before OR, causing some problems when you do not order your clauses or use parentheses to get what you want.  I encourage you to take this free training from Atlassian on tips/techniques to writing JQL queries to learn more: https://university.atlassian.com/student/path/849533-gain-project-insights-through-jql

For your query, this behavior can be addressed using status IN (A, B, C) such as with:

project = TEST 
 AND issueType = "Epic Feature"
 AND status IN ("New Request", "Solution Design", ...)
 AND NOT status CHANGED AFTER -2w
 ORDER BY Rank ASC

Next for your question on the number of days an issue has been sitting in a status, and without purchasing any addons from the marketplace, you will need a custom field to keep track of this.  Create a separate rule triggered on issue transition, and edit the field to be {{now}}  Whenever you need the number of days an issue has not moved within a rule, subtract the value from {{now}}.  If you need the value for reporting you will need a second custom field and a scheduled rule (e.g. run daily) to update the count of days.

Finally for your question on reporting on issues which have not moved in 15 day, it appears in your rule you have already implemented that.  What is not working as you expect?

Kind regards,
Bill