Advanced Jira issue search and filtering with logical and regular expressions in JXL sheets

[#1]

Hello community!

Jira is a big help in all sorts of use cases, including agile work and project management, dev ops, IT service management, manufacturing, product management, and business processes. Sometimes, especially in larger-scale Jira sites, it can get challenging to surface the right issues to track, manage, and report on. JXL for Jira is the all-in-one issue editor and organiser that helps you search, filter, and format issues based on even the most complex of requirements.

In addition to their JQL-based sheet scope, JXL sheets offer various styles of column filtering and level filtering. They support querying issues using logical statements, regular expressions, as well as targeting specific levels of issue hierarchy and custom structures. These capabilities, among others, make JXL an essential extension to Jira for everyone who wants to tame their tickets.

In this article, I’ll walk through different ways of slicing and dicing Jira data with the help of JXL.

Table search

You can filter contents of a sheet using the search field above the table. It optionally accepts logical statements, regular expressions, and supports targeting specific hierarchical levels.

Column filtering

To filter contents of a sheet, simply hover with the cursor over the header of the desired column and click the Filter column button that appears. In the menu that opens, type in a text, pick one or multiple field values, or select a number, date, or time range to filter by.

At the top of many columns' filtering menus, you can switch between different filter styles, such as Option filter, Text filter, Number filter, Date filter, Date and time filter, Time filter. You can switch most fields' column filter to a Text filter to significantly expand its search capabilities with logical statements, regular expressions, and more.

Logical statements

To enter a logical statement in a text search, make sure the logic icon in the search field is activated. You can then define disjunctions (OR), conjunctions (AND), negations (NOT), empty values (EMPTY, NOT EMPTY), exact phrases and escaping (" "), and group expressions to control operators' precedence. You can use logical statements in text column filtering, the table search field, and text conditions of conditional formatting.

This can be useful if you need to match issues that include some but exclude other values. Example: Give me all issues assigned to Engineering and Support teams, but not to Marketing, etc.

Regular expressions

To enter a regular expression, make sure the regex icon in the search field is activated. You can use regular expressions in text column filtering, the table search field, and text conditions of conditional formatting.

Regular expressions are useful if you need to execute a wildcard or fuzzy search on Jira issues, i.e. include or exclude issues with specified text, numbers, components, labels, versions, or other fields' values, where you can define a fragment or other pattern of the value. Example: Give me all issues where the version is equal or higher than “v3.1” or where the assignee ends in “[Acme]”, etc.

Target hierarchical levels

To exclude specific levels of an issue hierarchy or custom structure from your column filtering or table search, click the Choose levels icon in a column filtering menu or in the table search field. You can choose between applying the filter or search to all levels or only top, bottom, parent, or child levels.

This can be useful if you need to exclude sub-tasks or higher-level issues. Example: Give me all issues where the parent’s status category is “In progress”, or where child issues have story points, etc.

Level filtering

To generally hide issues without children or parents in an issue hierarchy or custom structure, click the Filter levels icon above the row handles to the left of the table. In the menu that opens, you can choose to hide top or parent level issues without children, or bottom or child level issues without parents. To only show issues that satisfy criteria of the enabled hierarchy or structure, activate the Hide issues outside levels checkbox.

This can be useful if your overall sheet scope and structure need to be broad, however, you’d like to narrow it down in particular views by excluding certain issues. Example: Hide all epics that don’t have any stories associated, or hide all stories that don’t have parent epics in scope, etc.

What’s JXL again? A Jira app that combines the power of Jira with the simplicity of spreadsheets. Features include creating and inline editing issues in highly customizable tables, copying and pasting fields individually or in bulk, grouping and structuring issues in custom hierarchies, summing up fields across levels, ranking issues, applying conditional formatting rules, and much more. It’s the all-in-one issue editor and organiser that saves you countless clicks and hours. Easier faster Jira.

If you haven’t already, take it for a free test drive for a month. Chances are you’ll never want to go back to vanilla Jira. Secure your free trial here on the Atlassian Marketplace.

I hope this was a useful tutorial. If anyone has any questions, please get in touch or comment. We’d love to hear from you. ✌️

---

Disclaimer: The author is on the JXL team ;)