Hello Jira Community, Could someone please help me fix my dashboard jql query? I am new to Jira (5 days of playing around) and I have a DB that I am trying to fix. I would like to show all tickets that have breached SLA but I would like to filter all tickets where Status = Resolved or all tickets where LOS Request Type = Vendor Setup. LOS Request type is a custom drop-down field for us. I have exported the manual excel file and based on my manual count i have 72 tickets breaching SLA. if I am to filter Resolved tickets and those under Vendor Setup, then I should only see 28. Below is the query I am using but it does not seem to properly filter for the two criteria. this query is giving me 64 instead of 28. can you please tell me if I am missing anything in my query? project = "10524" AND "Time to resolution" = breached() AND ("LOS Request Type[Dropdown]" != "Vendor Setup" OR status != Resolved) I don't see a way to attach a file, so I am just pasting the table below for the raw data. This is how I know that I should only be seeing 28 and not 64. Custom field (LOS Request Type)Issue keyIssue idStatusProject keyInvoice Payments or IssuesLOS-204451983PendingLOSVendor SetupLOS-203451974In ProgressLOSCL SupportLOS-202451893In ProgressLOSVendor SetupLOS-201451888In ProgressLOSVendor SetupLOS-199451859In ProgressLOSVendor SetupLOS-198451858Waiting for supportLOSVendor SetupLOS-197451857Waiting for supportLOSVendor SetupLOS-196451856Waiting for supportLOSVendor SetupLOS-195451855Waiting for supportLOSVendor SetupLOS-194451854Waiting for supportLOSVendor SetupLOS-193451853Waiting for supportLOSVendor SetupLOS-191451851Waiting for supportLOSVendor SetupLOS-190451850Waiting for supportLOSVendor SetupLOS-189451849Waiting for supportLOSVendor SetupLOS-188451848Waiting for supportLOSVendor SetupLOS-187451847In ProgressLOSVendor SetupLOS-186451846In ProgressLOSVendor SetupLOS-185451844In ProgressLOSVendor SetupLOS-184451843In ProgressLOSInvoice Payments or IssuesLOS-180451831Waiting for supportLOSVendor SetupLOS-179451829In ProgressLOSNew Matter CreationLOS-177451803In ProgressLOSInvoice Payments or IssuesLOS-176451793Waiting for customerLOSCL SupportLOS-175451785In ProgressLOSLegal Ops Project or TaskLOS-174451782In ProgressLOSForecastsLOS-172451763In ProgressLOSCLM SupportLOS-169451705PendingLOSCL SupportLOS-163451667In ProgressLOSContracts GeneralLOS-162451666Waiting for supportLOSInvoice Payments or IssuesLOS-161451657ResolvedLOSContracts GeneralLOS-147451605ResolvedLOSLegal Ops Project or TaskLOS-146451589Waiting for supportLOSReports and AnalysisLOS-145451586Waiting for supportLOSVendor SetupLOS-139451190In ProgressLOSCL SupportLOS-138450964Waiting for supportLOSCLM SupportLOS-136450530ResolvedLOSCL SupportLOS-116450509Waiting for supportLOSLegal Ops Project or TaskLOS-114450507Waiting for supportLOSLegal Ops Project or TaskLOS-112450505Waiting for supportLOSInvoice Payments or IssuesLOS-109450487ResolvedLOSContracts GeneralLOS-106450383ResolvedLOSInvoice Payments or IssuesLOS-98450201ResolvedLOSForecastsLOS-97450124Waiting for supportLOSVendor SetupLOS-95450122ResolvedLOSVendor SetupLOS-90450048In ProgressLOSCL SupportLOS-89450045In ProgressLOSCL SupportLOS-88450042ResolvedLOSInvoice Payments or IssuesLOS-85449956In ProgressLOSInvoice Payments or IssuesLOS-82449935In ProgressLOSVendor SetupLOS-79449824ResolvedLOSInvoice Payments or IssuesLOS-76449790In ProgressLOSCL SupportLOS-75449702ResolvedLOSInvoice Payments or IssuesLOS-74449701ResolvedLOSCL SupportLOS-71449622Waiting for supportLOSForecastsLOS-70449621ResolvedLOSInvoice Payments or IssuesLOS-68449571ResolvedLOSVendor SetupLOS-61449430ResolvedLOSVendor SetupLOS-59449359ResolvedLOSVendor SetupLOS-57449355ResolvedLOSForecastsLOS-55449147ResolvedLOS LOS-43448458Waiting for supportLOS LOS-42448457Waiting for supportLOSVendor SetupLOS-41448456In ProgressLOS LOS-40448455ResolvedLOS LOS-36448440ResolvedLOSCLM SupportLOS-32448309ResolvedLOSInvoice Payments or IssuesLOS-28448088ResolvedLOSInvoice Payments or IssuesLOS-27448086Waiting for supportLOS LOS-26448081ResolvedLOSCL SupportLOS-25448080ResolvedLOSInvoice Payments or IssuesLOS-22447987Waiting for supportLOSInvoice Payments or IssuesLOS-20447979CANCELEDLOS

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Dashboard JQL

jmercado

Hello Jira Community,

Could someone please help me fix my dashboard jql query? I am new to Jira (5 days of playing around) and I have a DB that I am trying to fix.

I would like to show all tickets that have breached SLA but I would like to filter all tickets where Status = Resolved or all tickets where LOS Request Type = Vendor Setup. LOS Request type is a custom drop-down field for us.

I have exported the manual excel file and based on my manual count i have 72 tickets breaching SLA. if I am to filter Resolved tickets and those under Vendor Setup, then I should only see 28.

Below is the query I am using but it does not seem to properly filter for the two criteria. this query is giving me 64 instead of 28. can you please tell me if I am missing anything in my query?

project = "10524" AND "Time to resolution" = breached() AND ("LOS Request Type[Dropdown]" != "Vendor Setup" OR status != Resolved)

I don't see a way to attach a file, so I am just pasting the table below for the raw data. This is how I know that I should only be seeing 28 and not 64.

Custom field (LOS Request Type)	Issue key	Issue id	Status	Project key
Invoice Payments or Issues	LOS-204	451983	Pending	LOS
Vendor Setup	LOS-203	451974	In Progress	LOS
CL Support	LOS-202	451893	In Progress	LOS
Vendor Setup	LOS-201	451888	In Progress	LOS
Vendor Setup	LOS-199	451859	In Progress	LOS
Vendor Setup	LOS-198	451858	Waiting for support	LOS
Vendor Setup	LOS-197	451857	Waiting for support	LOS
Vendor Setup	LOS-196	451856	Waiting for support	LOS
Vendor Setup	LOS-195	451855	Waiting for support	LOS
Vendor Setup	LOS-194	451854	Waiting for support	LOS
Vendor Setup	LOS-193	451853	Waiting for support	LOS
Vendor Setup	LOS-191	451851	Waiting for support	LOS
Vendor Setup	LOS-190	451850	Waiting for support	LOS
Vendor Setup	LOS-189	451849	Waiting for support	LOS
Vendor Setup	LOS-188	451848	Waiting for support	LOS
Vendor Setup	LOS-187	451847	In Progress	LOS
Vendor Setup	LOS-186	451846	In Progress	LOS
Vendor Setup	LOS-185	451844	In Progress	LOS
Vendor Setup	LOS-184	451843	In Progress	LOS
Invoice Payments or Issues	LOS-180	451831	Waiting for support	LOS
Vendor Setup	LOS-179	451829	In Progress	LOS
New Matter Creation	LOS-177	451803	In Progress	LOS
Invoice Payments or Issues	LOS-176	451793	Waiting for customer	LOS
CL Support	LOS-175	451785	In Progress	LOS
Legal Ops Project or Task	LOS-174	451782	In Progress	LOS
Forecasts	LOS-172	451763	In Progress	LOS
CLM Support	LOS-169	451705	Pending	LOS
CL Support	LOS-163	451667	In Progress	LOS
Contracts General	LOS-162	451666	Waiting for support	LOS
Invoice Payments or Issues	LOS-161	451657	Resolved	LOS
Contracts General	LOS-147	451605	Resolved	LOS
Legal Ops Project or Task	LOS-146	451589	Waiting for support	LOS
Reports and Analysis	LOS-145	451586	Waiting for support	LOS
Vendor Setup	LOS-139	451190	In Progress	LOS
CL Support	LOS-138	450964	Waiting for support	LOS
CLM Support	LOS-136	450530	Resolved	LOS
CL Support	LOS-116	450509	Waiting for support	LOS
Legal Ops Project or Task	LOS-114	450507	Waiting for support	LOS
Legal Ops Project or Task	LOS-112	450505	Waiting for support	LOS
Invoice Payments or Issues	LOS-109	450487	Resolved	LOS
Contracts General	LOS-106	450383	Resolved	LOS
Invoice Payments or Issues	LOS-98	450201	Resolved	LOS
Forecasts	LOS-97	450124	Waiting for support	LOS
Vendor Setup	LOS-95	450122	Resolved	LOS
Vendor Setup	LOS-90	450048	In Progress	LOS
CL Support	LOS-89	450045	In Progress	LOS
CL Support	LOS-88	450042	Resolved	LOS
Invoice Payments or Issues	LOS-85	449956	In Progress	LOS
Invoice Payments or Issues	LOS-82	449935	In Progress	LOS
Vendor Setup	LOS-79	449824	Resolved	LOS
Invoice Payments or Issues	LOS-76	449790	In Progress	LOS
CL Support	LOS-75	449702	Resolved	LOS
Invoice Payments or Issues	LOS-74	449701	Resolved	LOS
CL Support	LOS-71	449622	Waiting for support	LOS
Forecasts	LOS-70	449621	Resolved	LOS
Invoice Payments or Issues	LOS-68	449571	Resolved	LOS
Vendor Setup	LOS-61	449430	Resolved	LOS
Vendor Setup	LOS-59	449359	Resolved	LOS
Vendor Setup	LOS-57	449355	Resolved	LOS
Forecasts	LOS-55	449147	Resolved	LOS
	LOS-43	448458	Waiting for support	LOS
	LOS-42	448457	Waiting for support	LOS
Vendor Setup	LOS-41	448456	In Progress	LOS
	LOS-40	448455	Resolved	LOS
	LOS-36	448440	Resolved	LOS
CLM Support	LOS-32	448309	Resolved	LOS
Invoice Payments or Issues	LOS-28	448088	Resolved	LOS
Invoice Payments or Issues	LOS-27	448086	Waiting for support	LOS
	LOS-26	448081	Resolved	LOS
CL Support	LOS-25	448080	Resolved	LOS
Invoice Payments or Issues	LOS-22	447987	Waiting for support	LOS
Invoice Payments or Issues	LOS-20	447979	CANCELED	LOS

Accepted answers

Mark Segall

Hi @jmercado - The issue is in your "OR" statement. With the OR clause, you're telling Jira to return Vendor Setup OR return anything unresolved. Change the OR to AND and see if this returns the desired results:

project = "10524" AND "Time to resolution" = breached() AND "LOS Request Type[Dropdown]" != "Vendor Setup" AND status != Resolved

All comments

Mark Segall

project = "10524" AND "Time to resolution" = breached() AND "LOS Request Type[Dropdown]" != "Vendor Setup" AND status != Resolved

jmercado

Thanks, Mark.

that is actually what I was looking for. all tickets that are NOT Vendor Setup or NOT Resolved (all Unresolved). Actually, since I cannot fix this yesterday, I ended up doing the basic search. In the field dropdowns, I unchecked my filters (that is vendor set up and Resolved). Below is the JQL I got in the background. It does not look pretty (it's too long), but looks like it worked for my case. Would you comment again please if this is unsafe or not ideal to do?

[query intentionaly deleted]

Mark Segall

The thing to keep in mind with OR is that it can be deceptive when searching for the negative. Let's break down your original JQL:

project = "10524" AND "Time to resolution" = breached() AND ("LOS Request Type[Dropdown]" != "Vendor Setup" OR status != Resolved)

Project = "10524"
- You're telling it to look at a single project
AND "Time to resolution" = breached()
- Within the project find everything breached
AND ("LOS Request Type[Dropdown]" != "Vendor Setup" OR status != Resolved)
- Within the project everything breached and
  - all issues that are not tagged as Vendor Setup (any status)
    OR
    any issues where status is not resolved regardless of Vendor Setup

Now your new query that was created by basic view translates to this (which is very similar to the one I provided in my previous response:

Project = LOS and status Not IN (Resolved) and "LOS Request Type[Dropdown]" NOT IN ("Vendor Setup") AND "Time To Resolution" = Breached()

Let's break this one down:

Project = LOS
- Still telling it to look at a single project
AND status Not IN (Resolved)
- Within the project all issues that are not resolved
AND "LOS Request Type[Dropdown]" NOT IN ("Vendor Setup")
- Within the project all issues that are not resolved and not tagged Vendor Setup
AND "Time To Resolution" = Breached()
- Within the project all issues that are not resolved, not tagged Vendor Setup, and in breach

jmercado

Thanks so much. I changed the formula and copied yours and I got the same result (compared to my long basic query). I will use this formula moving forward. Thanks as well for patiently breaking down the details of the JQL query. That's really helpful.

Thanks again and have a great day, @Mark Segall

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jql

jira-service-management-cloud

jira-service-management