Filter with grouping

[Dima Astahov]

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

[Imran Shafique]

Hi Dima,

Yes, it is possible to achieve this using JQL (Jira Query Language). You can use the `assignee` field and the `IN` operator to filter issues assigned to users with at least one active ticket on their board. Here's an example query:

`assignee IN (usersWithAtLeastOneActiveIssue())`

This will return a list of users who have at least one active issue assigned to them.

If you want to group the results by user, you can use the `GROUP BY` clause:

`assignee IN (usersWithAtLeastOneActiveIssue()) GROUP BY assignee`

This will group the results by the assignee (user) and show the number of active issues for each user.

Note: The `usersWithAtLeastOneActiveIssue()` function is a custom function that you need to define in your Jira instance. You can do this by going to Issues > Custom Fields > User Fields and creating a new user field with the following JQL query:

`issueFunction in hasActiveIssues()`

This will create a new field that shows users with at least one active issue.

I hope this helps! Let me know if you have any further questions.

[Ratom]

Thanks Imran for info. Let's connect for little demo if psbble.

[Hannes Obweger - JXL for Jira]

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:

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]

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

Best

Dima

[Dima Astahov]

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

[Hannes Obweger - JXL for Jira]

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]

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]

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]

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]

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]

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 distribution  

[Hannes Obweger - JXL for Jira]

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.)

[John Funk]

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]

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]

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

[Dima Astahov]

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]

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

[Ratom]

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

[Dima Astahov]

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

[Matt Parks]

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]

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]

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]

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]

Here's a link to the Structure plugin (for Cloud) on the marketplace:

https://marketplace.atlassian.com/apps/34717/structure-ppm-jira-portfolio-management-tempo?hosting=cloud&tab=overview