Combining data from multiple projects in a filter

[Mike Miller]

We have multiple projects setup in Jira - "Global", "Modernization", etc.  These are the projects that represent active work, or active projects.  We also have a separate project for the intake of new ideas, suggestions, etc. called "Discovery."  A new idea in Discovery includes a field that indicates which department submitted the idea - Sales, Finance, etc.  Once an idea is activated and becomes a working project, an initiative is created n one of the other projects, and that's where we keep track of status as we move forward. 

When the initiative is created, that initiative ID is added to the idea, but the Sponsoring Department field from the Idea isn't carried over to the initiative; it is only on the idea.  The problem I have is how to create a filter of active projects (from "Global", "Modernization" etc.) and filter /group it by the Sponsoring Department (from "Discovery").   Any help would be appreciated.

[Brant Schroeder]

@Mike Miller Welcome to the Atlassian community

If the information about the sponsoring department is important, I would suggest adding that field to the other projects so it is retained, and you can easily find it on the work item(issue).  In order to link the initiative id you would need an app like scriptrunner or you will need to export it to excel and run some filters on it there.  You could also look at creating an link between the two work items (issues) https://support.atlassian.com/jira-software-cloud/docs/link-issues/   You could then filter on the link and do some work in the Excel export to get the information you need.

[Mike Miller]

I agree the most straightforward approach would be to add Sponsor to the initiatives in the other projects, but I don't have the ability to add fields to work items.  I was hoping JQL could handle this in a filter; by your comment about other apps, should I take it that this can't be done in JQL?

[Marc - Devoteam]

Hi @Mike Miller 

Welcome to the community.

It's not that I'm not willing ot help you out, but I think its very useful to learn how JQL works.

You can watch this video on advanced JQL, https://www.youtube.com/watch?v=Bbvl9PqCePA 

Or take a look ate the advanced JQL documentation, what-is-advanced-search-in-jira-cloud