Correct JQL query for the chart that shows last 7 days issues resolved vs unresolved

[Abdul Kader Broachwala]

Hello,

I have created a report that shows issues resolved versus unresolved for the last 7 days. However, I see the report isn't working correctly. At the moment, all the issues are showing as unresolved on the graph even if the issue status is "Done". Please find the attached screenshot. The JQL query I am using is  - project = "Deployment Board" AND status = "To Do" AND status = Backlog OR status = "In Progress" OR status = Done OR updated >= -7d ORDER BY created DESC

I am not sure if I need to tweak my JQL query to populate the desired results. I would highly appreciate it if anyone could please help with my issue. Thank you in advance for your help.

[Vishwas · Accepted solution]

Hey @Abdul Kader Broachwala 

The JQL query looks fine. Problem might be with Resolution not being set on the issues. Please check that if the issues returned in the query have Resolution set or they are showing as "Unresolved" for "Done" status issues. In case they are shown as unresolved you may have to set the resolution then only it will appear as Resolved issues.

Regards,

Vishwas

[Abdul Kader Broachwala]

Hello Vishwas,

Thank you for your prompt response. 

Yes, you are correct. I noticed that the resolution is not set as I see issues that are marked as in "Done" are still sitting under "Unresolved". Please see the screenshot below. 

Could you please help me with how to set the resolution as "Resolved" for all "Done" issues, so on the graph, it will show Issues in "Red" that are unresolved versus in "Green" that are resolved? Thank you in advance for your help!

[Vishwas]

Hey @Abdul Kader Broachwala 

We need to set the Resolution for Done Issues. There are ways to do this one. Let me list out for you. But before that you may have to fix your workflow so that future issues don't end up like this. To fix workflow Navigate to that particular workflow --> In Edit Mode --> Add a Post function for transition that is going to Done for Set Resolution as Done(or an equivalent value in your instance). Please check out this thread where previously I had suggested a solution for similar type of issue https://community.atlassian.com/t5/Jira-Software-questions/Couldn-t-make-resolution-as-Done-for-completed-User-Stories/qaq-p/1897440

Now to set Resolution for existing Unresolved issues that are in Done Status.

1.  Try a "bulk edit", but you'll need to be very careful while doing this, as you have to put the resolution field on the edit screen to enable it, and if someone edits an issue while it is there, they may resolve issues which wouldn't have been the case for them.

2. If you have scriptrunner plugin you can bulk set resolution for the affected issues. You just have to save the filter(only for done which are marked as unresolved issues). Check out this post here which guides you how to do https://docs.adaptavist.com/sr4js/latest/features/built-in-scripts/bulk-fix-resolutions

3. In case you have automation for jira plugin write an automation to set the resolution. You may have to use "Scheduled" Event to this. The same which already I have mentioned in the above link  https://community.atlassian.com/t5/Jira-Software-questions/Couldn-t-make-resolution-as-Done-for-completed-User-Stories/qaq-p/1897440

Hope this helps to fix your issue !

Regards,

Vishwas

[Abdul Kader Broachwala]

****Please Ignore the below message****

Hi Vishwas,

Thank you for the explanation. Unfortunately, I do not see the post-function option when I edit my workflow and click on "Done". Please see the screenshot below. Any idea, if there is an alternative way to add post-function? Please advise

[Vishwas]

Hey @Abdul Kader Broachwala 

You have to click on "Done" Transition to see Advanced Workflow Configurations.

Click on this highlighted part.

Please check out this link explained about post function configuration https://support.atlassian.com/jira-cloud-administration/docs/configure-advanced-issue-workflows/#Advancedworkflowconfiguration-postfunctions

Regards,

Vishwas

[Abdul Kader Broachwala]

Hi @Vishwas 

Thank you for the explanation. As per your advice. I have added the post-function to the transition "In-Progress to Done" and set the resolution as "Done". I also published my workflow again. Please see the screenshot below. To set Resolution for existing Unresolved issues that are in Done Status. I fall under option 1, I am selecting all the issues>clicking on Tools>Buld Changes>Only selecting issues that are Done but unresolved>Clicking on edit fields (However, I do not see a field to set resolution).

Could you please let me know if I am following the right steps? Also, for the future issues that are set to Done, will it automatically be resolved? Since now I have added the post-function in my current workflow? Please advise

[Vishwas]

Hey @Abdul Kader Broachwala 

Yes you are following the right steps and the configuration you have done looks fine.

Okay now to set resolution for unresolved issues, I hope you have the JQL ready only for unresolved issues ?

Did you try to add "Resolution" field to Edit Screen in Screens of the project and do Bulk Edit ? The field didn't show up on bulk edit page ?. I see it's not available for me also.

We have to try another method for case 1 we have to add a temporary transition from Done status to itself and set the resolution as "done".

Please check this out kb which has step by step action to be performed https://confluence.atlassian.com/jirakb/bulk-edit-resolutions-in-jira-server-321857142.html

By doing this I guess the "Resolved Date" is gonna change to current date, if that is okay please go ahead and to the Bulk Transition Method.

Regards,

Vishwas 

[Abdul Kader Broachwala]

Hi @Vishwas 

You're AWESOME!!

It worked. Please see the screenshot below. Thank you for your patience and great help!

[Vishwas]

Hi @Abdul Kader Broachwala 

Great !!, Glad that I was helpful !

Regards,

Vishwas

[Nic Brough -Adaptavist-]

There are three things I'd like to look at in this question.

1. You do not mention the resolution field at all.  Do you know that the right issues in here have their resolutions set at the right time in their workflow cycle?  To test this, try running your search in the standard search and check what the resolution field is across the results.

2. The screenshot is not of a "created vs resolved" gadget.  I wouldn't expect this gadget to be showing you created vs resolved, it actually looks right to me.

3. The complex one - when you mix "and" and "or" in a query, most humans get it wrong at first, and don't really get the logic.  It's not even well documented what the logic is in cases where you give Jira a potentially ambiguous query.

If you write out your query as a list of issues it is going to report on in English (I don't usefully speak any other languages apart from "gibberish" according to my family and friends, so I work in English, but the same idea can be done in most - translate your query into a list of separate groups of issues) - are you sure this is the right list?

project = "Deployment Board" AND status = "To Do" AND status = Backlog OR status = "In Progress" OR status = Done OR updated >= -7d ORDER BY created DESC

This will include issues that are:

• In the project "deployment board" and in the status of "to do" and in the status of "Backlog".  (Note - this means no issues will be found - issues cannot be in two status at the same time)
• In the status of "in progress" across all visible projects
• In the status of "done" across all visible projects
• Have been updated in the last week across all visible projects

I don't think that is right (it might be, but I doubt it).  I'd like to suggest you try:

Project = "deployment board" and (status in ("to do", "backlog", "in progress", "done") or  updated >= -7d)

This will get you a list of issues from that one project, in any of the four status you mention, plus issues in the project that have been updated in the last week, irrespective of status.

[Abdul Kader Broachwala]

Hello Nic,

Thank you for your response.

Thank you for the updated JQL query. After using the updated query the issue is still persistent. I believe this is something on my side that requires to be corrected. I think I need to set the resolution for all the issues that are marked as "Done" for the graph to work properly. I have shared the details below when I responded to Vishwas. I would appreciate it if you could please guide/help me with the same. Thank you in advance for your help!