How to get the sum of tickets in a filter per priority and team using Smart Values

[Howell]

I have a follow up query regarding the post below:

Solved: How to get the sum of tickets in a filter per prio... (atlassian.com)

 We have a custom drop-down field which lists down the project's teams.  How can the content shown below be generated and sent out as an email?

 Team       Critical    High    Total

A                      1          1          2

B                       2         3          5

Total                 3         4           7

There are almost 20 Teams, is there a way to loop or do I have to create separate lookups for each of the values.

Thanks in advance.

[Bill Sheboy · Accepted solution]

Hi @Howell 

Looping would not work because a smart value, list iterator can only "see" the data from the scope of the iterator, and lower.  For example, when iterating over a Lookup Issues result, only data in the lookup can be seen.  (i.e., not any looping variable like your Team and Priority values)

A work-around to do this with build-in features is smart value, list filtering: https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588

The email action would need to filter for each of your criteria pairs of Team and Priority, at the appropriate location within your table.

Kind regards,
Bill

[Howell]

@Bill Sheboy 

Thanks for your reply.  My understanding from above is that my automation steps will be:

1. When: Scheduled and run a JQL search which returns all the required data.

2. Then: Send email.  All the filtering and counting will be done here.

Is it correct?

It's a bit complicated for me and I am now looking at sending the report via eazyBI.

[Bill Sheboy]

For something like this, eazyBI would work.  If you have already built that, no need to read further in my response.  Otherwise...

You are correct that a work-around like this can be complicated, and it helps when additional tools like eazyBI are not available.

Let's first confirm which version of Jira are you using: Cloud, Server, or Data Center?  Your post seems to indicate Server version.

The version impacts which features are available for rules:

• Cloud and Data Center have the Lookup Issues action, but Server does not
• Server and Data Center have the bulk-handling feature of triggers / branches, but Cloud does not
• All three have smart value, list filtering

If you have Lookup Issues, I recommend using a Scheduled trigger with no JQL.  The lookup may use the JQL instead.

Then each Team / Priority pair in the table would count the issues.  For example:

{{#=}}0{{#lookupIssues}}{{#if(and(equals(team.name,"A"),equals(priority.name,"Critical")))}}
+1{{/}}{{/}}{{/}}

How this works:

1. iterating over the lookup issues results
2. filter for the items with a Team of "A" and a priority of "Critical"
3. adding a +1 when there is a match
4. wrapping all of that in a math expression, with a default value of 0

This would be repeated / adjusted for each element in your table.

[Howell]

@Bill Sheboy 

I am using Data Center version.  I observed that lookupIssues support only a limited number of properties.  I used Issues instead and I was able to produce the report.

Thank you very much for your help!

[Bill Sheboy]

Awesome; I am glad to learn that worked for you!

I believe the bulk-handling of {{issues}} for Jira Server and Data Server handle all of the fields, but Lookup Issues only handles a few currently for those Jira versions. 

When that action was first added for Jira Cloud, it only supported about a dozen fields, and about a year later the remaining fields were added.  That may happen for Server and Data Center also, as in this suggestion: https://jira.atlassian.com/browse/JIRAAUTOSERVER-877

[Marc - Devoteam]

Hi @Howell 

Yes, you would.

This is more a BI related question on combining information from 2 different data points.

You could also look into marketplace apps that enhance your dashboard options, like; Custom Charts for Jira.

They have extensive gadgets to display the information and the opportunity to have an overarching gadget to change all relate charts baes on input.

p.s. I don't work a this vendor of Custom Charts, but use the app in my daily work.

[Howell]

Thanks Marc!

I was able to create the report using eazyBI and I'm persuading the user to use it instead of Jira Automation.  The requirement piqued my curiosity, and I am interested to know if the email report could indeed be created using Jira Automation.