Filter with results from 2 projects side-by-side

Alain Beaulieu
Contributor
October 28, 2024

Hi,

We have a JSM project and a standard Kanban company-managed Jira project. When a JSM issue needs higher level support, we create a corresponding issue in the Jira project and we link the two together ("Relates To").

One user asked me (I'm Jira admin) to create a filter that would return a list of issues from the JSM project having a link to an issue in the Jira project, and show it this way:

JSM Key|Jira Key|JSM Assigned To|Jira Assigned To|JSM Status|Jira Status

We have ScriptRunner and I'm familiar with issueFunction in linkedIssuesOf, but I don't think it will be possible to have a filter with data from two different projects on the same line. This would be trivial in SQL but in JQL?

Anyone would know by any chance?

3 answers

1 vote
Mohamed Benziane
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 28, 2024

Hi,

You're right; you won’t be able to display information from multiple issues in one line unless all the info is consolidated into a single ticket.

You could try using the Jira Cloud app in Excel to pull data, which would allow you to work with the values directly in Excel.

Alain Beaulieu
Contributor
October 28, 2024

Thanks @Mohamed Benziane . I'm not familiar with that Excel plugin but I can have a look.

Like Mohamed Benziane likes this
0 votes
Hannes Obweger - JXL for Jira
Atlassian Partner
October 28, 2024

Hi @Alain Beaulieu

if you're open to solutions from the Atlassian Marketplace, you'd get pretty close using the app that my team and I are 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 range of advanced features, including support for (configurable) issue hierarchies. These issue hierarchies can be based on Jira's built-in parent/child relationships (like task/sub-task, or epic/story), and/or based on issue links of configurable issue link types.

With this, you can build a cross-JSM-JSWM like e.g. this in just a couple of clicks:

jsm-jsw-overview.gif

In this case, the JSW tickets (bugs) are connected to their parent JSM tickets via issue links of type relates to.

Custom issue hierarchies also play well with JXL's other advanced features, such as issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes

Alain Beaulieu
Contributor
October 29, 2024

Hello @Hannes Obweger - JXL for Jira

Of course I'm always open to plugins, we already use a good dozen. But I've been diplomatically informed by higher management that we will not purchase any other Jira plugin for the time being.

That being said, your product looks very interesting and powerful. I will certainly keep a shortcut to it in my notes. Eventually, we never know. And I think the use case would be easy to make.

Thanks for sharing!

Alain Beaulieu
Contributor
October 29, 2024

@Hannes Obweger - JXL for Jira , I'm curious. I'm guessing the table you showed is based on some sort of query. I can clearly see the hierarchy you mentioned. How would you write that query and is it in JQL?

Hannes Obweger - JXL for Jira
Atlassian Partner
October 29, 2024

Hi @Alain Beaulieu

yes, every sheet is based on either a JQL statement or a saved filter (the sheet scope). For your use case, we'd need a query that pulls in the relevant JSM issues, and the linked JSW issues. (It's not a big deal if we pull in some additional issues, as we can filter them out in JXL.) JXL will then sort out the hierarchy between the issues based on your configured hierarchy (the so-called structure) and display them accordingly.

If you're interested, you can see JXL in action here.

Best,

Hannes

0 votes
Nikola Perisic
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 28, 2024

Hi @Alain Beaulieu 

You can use something like:

project in ("Advanced IT Service","Customer Support")

Replace the project names in quotes with your project name. That with the combination of: https://support.atlassian.com/jira-software-cloud/docs/jql-functions/ where you would look for linkedissues() function.

Alain Beaulieu
Contributor
October 28, 2024

Thanks @Nikola Perisic but that's not the issue. The issue is the filter columns returned. How can you show the keys/ids of the issues from both projects side by side in the results? Plus the statuses, plus the respective Assigned To?

If my JQL function has key in it, which project does it come from?

In SQL I would write SELECT JSM.key, Jira.key, JSM.Status, Jira.Status... etc.

I don't know how to have the keys, statuses, assigned to values from both projects returned on the same line. I don't think it's possible but I'm hoping someone would have a magic trick.

Nikola Perisic
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 28, 2024

JQL is not based on SQL even though it has some similarities. But the results are being shown in the format of issues. In Data Center you can use SQL since its hosted on own database by you, and you can show the data like you've just described. With JQL in cloud, this works only based on the issue search results.

Alain Beaulieu
Contributor
October 28, 2024

Right.

Nikola Perisic
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 28, 2024

When you mention if the JQL has a key in it, you can see it when you navigate to your Project section. Anytime you can search by the project key as well. You cannot show the issues side by side, but you can show the issues from both projects in one query search.

Alain Beaulieu
Contributor
October 28, 2024

I've been using Jira since around 2015. I know what the key is. It's the issue number/identifier. When I wrote "If my JQL has key in it", I meant to say having the column called KEY visible in the filter results.

That being said, what do you mean by "you can show the issues from both projects in one query search"?

Nikola Perisic
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 28, 2024

To answer your question: No, there is no column named key. With JQL search results you get the issue keys which is something else from your requirement. 

What I've meant is that when you enter the query project in ("Project A", "Project B") is that it will show the issue results from both of these projects.

Alain Beaulieu
Contributor
October 28, 2024

I was not "asking the question" whether or not there is a column named key.

2024-10-28_13-04-35.jpg

Suggest an answer

Log in or Sign up to answer