Filter with grouping

Dima Astahov April 25, 2024

Hi, 

I'm trying to set a Filter and Subscription to get :

<<List of users with at least one active JIRA ticket on their board>>

Is it possible in JQL, something like  TOP 1  or group by user?

 

Thanks

Dima

4 answers

1 vote
Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 25, 2024

Hi @Dima Astahov

if you're open to solutions from the Atlassian Marketplace, you may want to have a look at 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 issue grouping by any issue field(s).

This is how it looks in action:

group-by-category-v3.gif

This is grouping by a Category field, but it works for any other fields, too. Issue grouping can also be combined with JXL's other advanced features, such as support for (configurable) issue hierarchies, sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes

Dima Astahov April 25, 2024

Hallo  Hannes !!! Thanks so much, I'm  IN.  Probably need some time to digest.

Best

Dima

Dima Astahov April 25, 2024

Exploring sheet now, trying to mimic you demo to make counts, but looks like I'm missing some controls.

Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 26, 2024

Hi @Dima Astahov

the setup in my demo above is relatively simple; I've changed the sheet scope and the sheet columns to include the data that I'm interested in, enabled sum-ups, and then (as shown in my clip) perform grouping by one of the available columns/fields. 

If you want to see a full demo of the app, I'd recommend having a look at this quick demo video. It gives a great overview. 

Let me know if this helps,

Best,

Hannes

Dima Astahov May 6, 2024

Thanks Hannes, need more time to digest all features. I'm able to produce totals by column, but  need collapse tree for nice view.  But  I noticed that there is  10K rows limit on creating Sheet, is this true ?  for my case I have 16K issues,  is it possible to apply filter/jql to produce Sheet ?

 

Tx

D

Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
May 6, 2024

Hi @Dima Astahov

we can increase this limit for you, no problem. Mind reaching out to us via https://jxl.app/support so we can trigger the process with you?

Best,

Hannes

Dima Astahov May 6, 2024

Thanks Hannes ,for sooo super quick support !!! For now limit is OK, it's not a main problem. So when I create the right view out of Sheets, can I plug it into  native Jira distribution to send out ?

Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
May 6, 2024

Hi Dima,

understood - raising the limit is also no problem whatsoever for us, so please just reach out whenever you bump into it!

Regarding 

 

can I plug it into  native Jira distribution to send out 

Not quite sure I understand what you mean by native Jira distribution - but every view has a unique URL (you can either just copy/paste the URL from your browser, or use JXL's share dialog to obtain it) that can be sent to any user that has access to your Jira site (and the respective sheet). They will land on the exact view that you sent to them. You can also embed a JXL view e.g. into Confluence, if that'd be of value for you.

Hope this helps,

Best,

H

 

Dima Astahov May 7, 2024

Thanks again H!
So nice support,  need to create this format. Afraid that I can not do it exactly, but I can create separate sheets for each metrics.

I meant that I planned to use email distribution available in Jira under distributionimage.png  

 

Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
May 7, 2024

Right - yes, I think you'd want to create different sheets, or perhaps different saved views within one sheet, with respective column filters active.

> use email distribution in Jira under distribution

Understood - no, JXL doesn't currently integrate with Jira's built-in email distribution system; for the time being, you'd have to send out such emails yourself. (It's an interesting idea though, and I've just added it to our internal idea board.)

0 votes
John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 29, 2024

Hi Dima,

If you are you wanting to see the group by for the Assignee, just create your Filter like it already sounds like you have done. 

Then create a Dashboard or edit an existing one and add a gadget for Issue Statistics. Connect it to your filter. Then use Assignee as the Issue Statistic. 

That should get you want you want - and no add-on or Excel dump needed.   :-)

Dima Astahov May 6, 2024

Thanks John,

Need more time to digest all solution, but I'm not sure if my filter will work, confirmed that there is no aggregation available in JQL.

Dima Astahov May 6, 2024

Sorry, John, can you elaborate how create right filter ?

Dima Astahov May 7, 2024

I learned that I can not create any aggregations with JQL, not clear, how I can do my summary with filter, it will have 1 row = 1 issues, so will be multiple rows for each assignee.

John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 8, 2024

Sorry, I thought you just wanted a count of issues per assignee. You are looking for multiple items were assignee. 

Ratom May 13, 2024

Thanks John for your help, doing my home work with all suggestions now.

Like John Funk likes this
0 votes
Dima Astahov April 25, 2024

Looking at the sheet now, trying to mimic you demo, to count number of assignees, but looks like I'm missing some controls comparing with your demo above, 

Tx again, will need more time to practice.

Best

D

0 votes
Matt Parks
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 25, 2024

You could add ORDER BY Assignee (ASC or DESC, depending on how you want to view it) to the end of the JQL to group by the Assignee field.

Dima Astahov April 25, 2024

Hi Matt, 

I did play with filter JQL, I still can not get 1 line = 1 user structure , with ORDER BY  I have 1 issue = 1 line, so user with 10 issues will get 10 rows.  And looks like there is not GROUP BY in jql, can you provide more details/sample

Matt Parks
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 25, 2024

No, there is no Group By in JQL. If you had Structure or a similar plugin, you could use the JQL to populate a Structure and then use their Group functionality.

Another option would be to export the query results to an Excel file and do the grouping there, although I generally don't like to take data out of Jira into Excel.

What problem are you trying to solve?

Dima Astahov April 25, 2024

Thanks Matt,  I need to setup filter  with subscription to generate list of distinct users having at least one active issue (not Done). Ideally in full Auto mode without dealing with export to excel.

I'm on the cloud standard, went thru few add ons, but looks like they work only on server. https://marketplace.atlassian.com/apps/1218767/

I'm going to check <Structure or similar plugin>. Appreciate if you point me to it,

Overall is this doable just with plugin? I can't explore an option with any extra dev otside of Jira.  

Thanks

Dima

 

 

 

 

 

Matt Parks
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 26, 2024

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
FREE
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events