Email Automation: Reporting a list of Epics & their children by issue type

[Alex]

Hi All - 

This one has me stumped. I am trying to create an email report that displays similar to this:

Epic Summary - Epic ABC

• Pull data from epic (no problem here)
• List of Child Issue type=Risk - Summary | Impact | Mitigation Plan | Assignee
• List of Child Issue type =Decision - Summary | Assignee

Epic Summary - Epic XYZ

• Pull data from epic (no problem here)
• List of Child Issue type=Risk - Summary | Impact | Mitigation Plan | Assignee
• List of Child Issue type =Decision - Summary | Assignee

As I said, no problem handling the epic & data. I can cycle through epics fine, and pull their issue data. I cannot figure out how to pull a list of child issues and put them in an email. 

Here is the screenshot of the basic automation. I have removed any attempt to pull the children because everything I have done has failed.

I feel like there must be a way to use multiple lookup tables, but I can't seem to get them to work.

[Manoj Gangwar · Accepted solution]

Hi @Alex 

Build the Automation Rule:

1. **Trigger:**

   - Set the **Trigger** to whatever event makes sense for you (e.g., a scheduled trigger or manual trigger).

2. **Branch Rule for Each Epic:**

   - Add a **Branch Rule** for **Issues of Type = Epic**.

     - This will loop through each Epic and allow you to pull data related to it.

3. **Lookup Child Issues for Each Epic:**

   - Use **Lookup Issues** to find the child issues linked to each Epic.

     - Add a **Lookup Issues** action, using a JQL query to filter for specific child issue types like `Risk` or `Decision`:

       - For **Risks**:

         ```JQL

         "Epic Link" = {{issue.key}} AND issuetype = Risk

         ```

       - For **Decisions**:

         ```JQL

         "Epic Link" = {{issue.key}} AND issuetype = Decision

         ```

4. **Create a Smart Value Table for the Email:**

   - Inside the **Send Email** action, you'll use smart values to loop through the `Lookup Issues` results and format the email content.

5. **Building the Email Content:**

   - In the email body, you can pull data for each child issue by iterating over the results of the Lookup Issues for both "Risk" and "Decision." Here's how you can structure the email body:

   ```plaintext

   Epic Summary: {{issue.summary}} - Epic Key: {{issue.key}}

   -- Risks --

   {{#lookupIssues}}

   {{#if(equals(issueType, "Risk"))}}

   Summary: {{summary}}

   Impact: {{customfield_Impact}}

   Mitigation Plan: {{customfield_Mitigation Plan}}

   Assignee: {{assignee.displayName}}

   {{/}}

   {{/lookupIssues}}

   -- Decisions --

   {{#lookupIssues}}

   {{#if(equals(issueType, "Decision"))}}

   Summary: {{summary}}

   Assignee: {{assignee.displayName}}

   {{/}}

   {{/lookupIssues}}

   ```

   - Replace `customfield_Impact` and `customfield_Mitigation Plan` with the actual field names or custom field IDs used in your Jira instance for Impact and Mitigation Plan.

[Alex]

Hi - first, thank you all for giving some feedback on this.

If there is a JIRA addon that exists that will do that, I would much rather that - seems like this is not exactly clear functionality.

@Bill Sheboy  - I am looking to get everything in a single email, and right now it is just a handful of epics. Less than 30 for sure (about 10). Right now anything I am doing is triggering multiple emails

@Manoj Gangwar  I am still struggling a bit though with creating the lookup table and leveraging it. 

I don't think any of the data is getting into the lookuptable - but I know the lookup itself is working. It is pulling what i would expect in the audit log.

The end result is giving me the epic info, but where "Project Risk" data is expected it is blank in the email

[Bill Sheboy]

Hi @Alex 

Short answer: To generate this actual report format you describe, you will need a marketplace app.  As a workaround, there are a couple of methods with automation rules for a limited number of epics / child issues.

Regarding workarounds...

Do you want this in a single email?  If so, with out-of-the-box features of rules I only know two ways do accomplish to get close to this report:

With a fixed / known number of Epics (This will be limited to around 30 epics.)

• trigger: scheduled
• For each known epic, add the following actions
  • action: Lookup Issues action to gather their child issues and the epic
  • action: Create Lookup Table for this epic, where the key is the Epic key / summary and the value is {{lookupIssues}}
• action: send email, using each lookup table to iterate the issues to build the email body

With an unknown number of Epics (This will be limited to 100 total issues spanning all epics.)

• Add a text, custom field to all child issue types for the Epics (e.g. for Story, Task, Bug).  This field will contain a sort key for the issue, containing: Epic Summary - Epic Key
• Create automation rules which sets the above custom field when an issue is created or its parent Epic is set
• Create your report rule
  • trigger: scheduled
  • action: lookup issues, with JQL to gather the issues needed, sorted by the custom field and any other fields
  • action: send email, iterating the issues to put them all in a single table with the custom field / sort key as the first one to designate the Epic

Kind regards,
Bill