Help in Grouping and Summing worklog as per issue and worklog Author in JIRA Automation Send Email

[Altamash Heroli]

• Reason for automation
  • Some issues have multiple worklog Author, due to which its difficult to tracking individual author's time. 
• Solution
  • Wrote JIRA AUTOMATION using a JQL to get the information and send email
• Problem
  • In the First table of Email, not able to Group the worklog Author and sum up his total hours as per the Issue
  • In the second table, not able to Group the worklog Author and sum up his total hours

My Email Body is as follows

My Resultant Email is as follows

Expected View should be

My Jira Automation is as follows

[Bill Sheboy · Accepted solution]

Hi @Altamash Heroli -- Welcome to the Atlassian Community!

Adding to the suggestions from @Valerie Knapp there are no built-in sort or group-by list functions to help with what you asked.  A marketplace app would be needed for advanced reporting like this.  However...

A workaround is possible if you know all possible work log author's and are okay with hardcoding them in the expression.  The approach would be:

• for a specific author, attempt to filter by their account ID (or displayName)
• when found, use a math expression to sum their timeSpentSeconds values

For example:

{{#lookupIssues}}
* {{key}} --
{{#if(worklog.author.displayName.flatten.distinct.match("(Altamash)").size.gt(0))}}
Altamash: {{#=}}(0{{#worklog}}{{#if(author.displayName.equals("Altamash"))}}+{{timeSpentSeconds}}{{/}}{{/}}){{/}} seconds; {{/}}
{{#if(worklog.author.displayName.flatten.distinct.match("(Bill)").size.gt(0))}}
Bill: {{#=}}(0{{#worklog}}{{#if(author.displayName.equals("Bill"))}}+{{timeSpentSeconds}}{{/}}{{/}}){{/}} seconds; {{/}}
{{/}}

Here is the key part of that expression, and it would be repeated for each possible worklog author.  I formatted it a bit to make it easier to explain.

{{#if(worklog.author.displayName.flatten.distinct.match("(Altamash)").size.gt(0))}}

  Altamash: 

    {{#=}}( 0
             {{#worklog}}
               {{#if(author.displayName.equals("Altamash"))}}
                 +{{timeSpentSeconds}}
               {{/}}
             {{/}}
          )
    {{/}} seconds; 

{{/}}

How that works is:

• Once inside of the Lookup Issues iterator, we want to filter for a specific person's work logs
• And so we merge together all of the worklog author display names using the new flatten function (which is not in the documentation yet)
• Using the distinct function we reduce that to eliminate any duplicate values
• With the match() function, we check if that person logged any time as the list size will be greater than 0
• Now that we know the person logged time, we want to sum that up...
• We nest another iterator inside over the worklog entries
• And using smart value, list filtering, we only get the worklogs for that person
• Adding their time spent in seconds for each entry found
• That is wrapped up in a math expression, with a 0 at the front as a default
• Finally adding a units of measure indicator of "seconds"

If you want to use this, repeat the section for each author, and experiment to add your table / row formatting.  "Pretty printing" the total time is much more difficult (although theoretically possible).  Instead I recommend picking a unit value and dividing; for example, divide and round to display in minutes or hours.

Kind regards,
Bill

[Altamash Heroli]

hie @Bill Sheboy 

Thanks for the help. 

Your Solution worked absolutely well.

Now my email gives me this result.

Could you help me get this into a tabular format?

Thanks
Altamash

[Bill Sheboy]

One simple version would use basic HTML for a table, as you did in your original question.  Adding that approach to the example I provider earlier, it could be this:

<table border="2" style="border-collapse: collapse;"
  <tr>
    <th>Work Item</th>
    <th>Worklog Author</th>
    <th>Time Logged</th>
  </tr>
  {{#lookupIssues}}
    {{#if(worklog.author.displayName.flatten.distinct.match("(Altamash)").size.gt(0))}}
      <tr>
        <td>{{key}}</td>
        <td>Altamash</td>
        <td align="right">
          {{#=}}ROUND((0{{#worklog}}{{#if(author.displayName.equals("Altamash"))}}+{{timeSpentSeconds}}{{/}}{{/}}) / 3600, 2){{/}}h
        </td>
      </tr>
    {{/}}
    {{#if(worklog.author.displayName.flatten.distinct.match("(Bill)").size.gt(0))}}
      <tr>
        <td>{{key}}</td>
        <td>Bill</td>
        <td align="right">
          {{#=}}ROUND((0{{#worklog}}{{#if(author.displayName.equals("Bill"))}}+{{timeSpentSeconds}}{{/}}{{/}}) / 3600, 2){{/}}h
        </td>
      </tr>
    {{/}}
  {{/}}
</table>

In this example, I divided the time value sums by 3600 seconds to display in fractional hours and display the work item key on every row. 

Depending upon how the HTML is used, using rowspan to merge rows and only include the key once may not work...and is challenging to implement due to the unknown ordering of the worklog entries preventing adding the key one time.

[Altamash Heroli]

@Bill Sheboy

Thanks a lot... I was able to get the desired result

Your Responses were very precise and up to the point

Thanks
Altamash

[Valerie Knapp]

Hi @Altamash Heroli , welcome to the Atlassian Community and thanks for your post!

Thank you for explaining thoroughly your use case. I understand what and why you are trying to achieve this.

I will just say, however, it's likely you will need an app to achieve this fully. Whatever you will be able to do with this automation will be a workaround, as opposed to having the support of an app such as Email this issue, which is purpose-built for this kind of thing. I am not paid to say this, by the way. I have just found that with the support of a vendor and their service desk, I have gotten to achieve much better results. If you have any budget for this, I would consider this app because, as I said, the support is also very good. https://marketplace.atlassian.com/apps/4977/email-this-issue?hosting=cloud&tab=overview 

Best wishes

[Altamash Heroli]

@Valerie Knapp 

Thanks for the quick response. 
But unfortunately, we dont have budget for this.

Is there is no way to loop the issue (within the email body) to group it by Group the worklog Author and sum up his total hours as per the Issue?

Thanks
Altamash

[SilkeS]

This topic has been locked because the thread has become a bit dated. If you’d like to keep the conversation going or have additional questions, we encourage you to start a new topic. You can read more about necro posting (“raising threads from the dead”) in our  Community Guidelines .

Thanks!

[Tim Ballingall]

FWIW - the only way i can get complete and reliable worklogs out of jira now is with VBA

I used to be able to do a direct DB query for this ( on prem )

I used to be able to use Tempo ( on prem ) until cloud license costs made that untenable

I use the jira add in for excel but that FAILS on an adhoc basis ( cant return all data  - sometimes )

I used to be able to use API query until Atlassian changed the query and forced me to recode. 
Recode is now in VBA. 

Seriously Atlassian, do you want customers to leave ?