JQL - XX

[Louis]

I have a Jira configuration where I need to write at JQL statement that gives me all issues  (user stories and tasks) with no data i particular columns

They should be grouped and displayed by the key and summary in their parents parent (Business Area/Capability)

I have this query but it it only gives me the Business Area list (47 issues) but I need ALL the Lovest level Tasks 2 levels below those 47 grouped by the 47 and ranked by the count of their number of issues per Business Area. Anyone can help me with what is wrong with 

My JQL?:

category = 'XXX' AND issueFunction in portfolioParentsOf("category = 'XXX' AND \"Program Increment (AA)\" = AAXX") AND issuetype = Business Area AND ("Attribute As" is EMPTY OR "Attribute B" is EMPTY) AND statusCategory != SomeValue

[Samuel Gatica _ServiceRocket_ · Accepted solution]

Hi @Louis 

To achieve the desired result in JQL, you need to create a more complex query that includes subqueries and aggregates the issues correctly. JQL itself doesn't support grouping and ranking directly within the query, so you might need to use a combination of JQL and external tools like Excel or a reporting plugin in Jira.

However, I can help you refine the JQL to get all issues (user stories and tasks) with empty values in particular columns and related to their parent "Business Area/Capability."

Here's an approach to get you closer:

Find all low-level issues (user stories and tasks) with empty fields.
Include the parent information in the results.
Since JQL doesn't support direct grouping or ranking, you may need to run these queries separately and then process the results externally.

*Step 1: Find all low-level issues with empty fields
To find all user stories and tasks with empty "Attribute A" or "Attribute B" fields within a specific category and program increment, use the following JQL:

category = 'XXX' AND "Program Increment (AA)" = AAXX AND issuetype in (Story, Task) AND ("Attribute A" is EMPTY OR "Attribute B" is EMPTY) AND statusCategory != SomeValue

*Step 2: Include parent information
To get the parent information for these issues, you can use a reporting tool or a Jira plugin that supports advanced JQL functions, such as ScriptRunner.

If you have ScriptRunner, you can extend the query to include parent information. Here's how you can do it:

issueFunction in issuesInEpics("category = 'XXX' AND \"Program Increment (AA)" = AAXX AND issuetype = Epic") AND ("Attribute A" is EMPTY OR "Attribute B" is EMPTY) AND statusCategory != SomeValue

This query finds all issues in Epics that match the criteria. However, it does not directly group or rank the results.

*Ranking by count of issues per Business Area
For ranking and grouping, you would need to export the results to a tool like Excel or use a Jira app that supports advanced reporting, such as eazyBI.

JQL has its limitations in terms of grouping and ranking directly within queries. For more complex reporting, consider using dedicated Jira reporting tools or plugins. The refined JQL provided should help you get the list of issues with empty fields and their parent Epics, which you can then process further in a reporting tool.

Hope this helps.

Best regards

Sam

[Louis]

Hi Sam. Thank you very much. This type of answer is exactly what I was looking for. Im new to JQL and trying to understand its inner logic, strenghts and weaknesses, so this really helps me understand why it was not so easy.. I come from an Oracle background with SQL and PL/SQL and thinking it should be easy to just pull out all the data you need from all levels of the hierarchy and then group and sort it.. After having read your text, I will try to find out which reporting tools we have in our toolbox. Not sure if script runner is one of them, but we have an integration to PowerBI. I guess PowerBI can do the job, but I'm (naturally) eager to get as close to the data and native toolset of Jira (JQL and what may exist) as I can... I will work with your advice, and find a solution I'm sure. Thanks a lot 👋🌞

[Louis]

Hi @Samuel Gatica _ServiceRocket_ I tried following your example but I get a compile error - not sure where the syntax goes wrong?..

[Samuel Gatica _ServiceRocket_]

Hi @Louis 

You need to close the quotes for field "Program Increment (PI)"

[Hannes Obweger - JXL for Jira · Accepted solution]

Hi @Louis

welcome to the community!

As mentioned by Samuel, JQL in itself can't do any groupings; the best that you can get is to sort your results (using the ORDER BY) clause, which would at least put issues into a meaningful order.

To get the exact result that you have in mind, you'll likely need extra tooling. As mentioned, you can obviously export your data e.g. to Excel and do some post-processing there. Alternatively, if you're open to solutions from the Atlassian Marketplace, you'll have more options. I myself happen to work on an app in which your use case should be relatively easy to solve; I'll provide more information below.

Best,

Hannes

[Hannes Obweger - JXL for Jira]

... and to expand on my last point: If you are open to solutions from the Atlassian Marketplace, I believe that your use case would be solvable using the app that my team and I 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 number of advanced features, including support for (configurable) issue hierarchies and various options for searching and filtering within these hierarchies.

With these, you can build a view like e.g. this in just a couple of clicks:

This is all issues with an empty "Assigned team" field, grouped by their parent epics, and including their sub-tasks. Is this roughly what you'd have in mind?

There's a few things to say about my exact setup (happy to elaborate if the above looks interesting), but put simply, I'm pulling in all issues that are potentially relevant to me, and then let JXL figure out the parent child relationships between them.

Any questions just let me know!

[Louis]

Dear Hans

Thanks a lot that looks veryb interesting indeed. Is this something you can interface to Confluence as well?

The query I am working on right now belongs to a data quality initiative. I actually need to get tasks/userstories that are lacking data in particular fields grouped and ranked by their parent epic - for instance in a cake diagram which will give a graphical view into which epics are parents of children with the biggest flaws - if that makes sense?..

We use datacenter Jira, do I have the option of testing the JXL plugin myself you think and how would I do it?

Best regards Nanna

[Hannes Obweger - JXL for Jira]

Hi @Louis

yes, you can embed JXL sheets into Confluence.

Regarding pie/cake charts, this isn't currently available in JXL - however you can configure virtually any (nested) groupings and field sum-ups. Your use case actually sounds like a great fit for JXL!

For testing JXL, the easiest is to just do the 1-month free trial that is available for all Marketplace apps (just hit "Try it free" on our Marketplace listing). If you want to limit access to JXL to just yourself or a smaller group of people during the trial, you can do that via JXL's permission system.

If you haven't yet, it may also be useful to check our website, https://jxl.app, as well the quick demo video that we've recorded.

If there's anything else we can assist with, please don't hesitate to reach out to us via https://support.jxl.app - we're here to help!

Best,

Hannes

[Louis]

Thank you Hannes - I will consider this among other plugins for our needs at a later stage. For the moment I think I will follow the Excel/PowerBI path, but I have noted your tips - thanks a lot :)