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 · Accepted solution]

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

[Rahul Jith]

Hello @Bill Sheboy ,

Hope you are doing great!

Apologize for the late response as I got into some emergencies from past couple of weeks.

Yes I do I applied the above logics and it worked. Thankful to you for the insight on JQL queries and link is really helpful. 

Cheers mate

Rahul Jith

[Rahul Jith]

Hi @Bill Sheboy ,

After applying the above mentioned JQL, I just noticed if I create a new issue type which by defaults appears in my 1st Status is also reflecting on the above query. I tried moving this new issue to other status and checked the ageing report which also showing this new one under the list. 

Tried an alternative by adding AND create <= 15d along with above query gives me an outcome with that new issue. Same logic when I tried with 30 days and 90 days ageing as AND create <= 30d and AND create <= 90d respectively.

Do you think this is a right query or should I reconstruct some other thoughts. Can you suggest me the best option to obtain this.

Regards

Rahul Jith

[Bill Sheboy]

Hi @Rahul Jith 

I am not quite following what you describe.  Are you noting that an issue which was just created is being returned by the query?  If so, adding a condition to check the created date should help.  If not, please post your complete current JQL; that may indicate what is happening.

Thanks!

[Rahul Jith]

Hi @Bill Sheboy 

Attaching the screenshot of the query that I have modified with created date <=30 days. 

Yes, without that, an issue which was just created is being returned by the query.

[Rahul Jith]

Hi @Bill Sheboy 

Is the above query mentioned by adding the created date is valid? I saw the newly created issues disappeared but still not so confident with results 😂

Need your expertise advice 

[Bill Sheboy]

The answer depends upon what you wanted to accomplish. 

With your addition, that query will ignore any issues created after 30 days ago.

[Rahul Jith]

Hi @Bill Sheboy 

The outcome I am expecting is to pull a report with the list of issues sitting in a particular status more than 15 or 30 days 

[Bill Sheboy]

You have written the query to answer your 30 days question.  If you change AFTER -30d to AFTER -15d you will have the other one.

Again I respectfully suggest: please consider taking the free training for JQL which I noted in my first post.  That will help you answer these and many other questions.  Thank you.