JQL Query for issues under initiatives

Robert Anthony_h2lqyo

Any assistance here would be appreciated. I am trying to publish a report in Confluence using  a Jira filter.   The report needs the following format. All the Epics are  in one Initiative. I am  able  to get all the  tickets using "Issuekey in childIssuesOf(InitiativeKey) "but not able to order as shown below.  If  you have a suggestion, please let me know:
Ticket                          Status      Status Notes
Epic 1                          In-Dev    Multiline text
Story 1 from Epic 1    In-Dev    Multiline text
Story 2 from Epic 1    In-Dev    Multiline text
Epic 2                          In-Dev    Multiline text
Story 1 from Epic 2    In-Dev    Multiline text
Story 2 from Epic 2    In-Dev    Multiline text

Ste Wright

Hi @Robert Anthony 

Natively, I'm not sure this is possible.

You can order by Epic Link - but this will just order all the story-level issues based on Epic Link, as opposed to each Epic with a list of stories below them.

There is a workaround, if you are using a Classic Project...

-----------------

Option 1 - Epic Issue Key

This workaround utilises a unique identifier common to each Epic and its children. It creates a UID at an Epic-level and then copies it to the Epic's children using Automation Rules.

The unique identifier can be used to order JQL results, and then a field unique to the Epic (Epic Name) is used to sub-order the issues with the Epic at the top.

Field:

• As a Jira Admin, go to Jira Settings > Issues > Custom Fields
• Create a Custom Field - type: Text Field (single line)
• Give the field a name - eg. UID

Automation - Rule 1:

• As a Project Admin, go to Project Settings > Automation
• Create a new rule:
  • Trigger: Multiple Issue Events - Issue Created, Issue Updated
  • Condition: Issue Fields Condition - Issue Type (field) equals (condition) Epic (value)
  • Action: Edit Issue - <<Custom Field>> - set value to {{key}}
• Give the rule a name and publish it

Automation - Rule 2:

• Create a new rule:
  • Trigger: Multiple Issue Events - Issue Created, Issue Updated
  • Condition: Advanced Compare Condition - {{issue.Epic Link}} (first value) does not equal (condition) - and leave "second value" Empty
  • Action: Edit Issue - <<Custom Field>> - Copy from Epic Issue (available via the 3-dots icon)
• Give the rule a name and publish it

Update:

• Update existing Epics and child issues to receive the appropriate data into the new field.
• I'd suggest doing this via Bulk Change - first search for all the Epics and bulk add, then bulk remove a label (for example)
• Repeat the process for all issues with an Epic Link

JQL:

Once the above is complete, use JQL such as this:

issue in portfolioChildIssuesOf("ABC-1") ORDER BY cf[12345] ASC, "Epic Name"

This is how the JQL works:

• The fields are:
  • ABC-1 is the Initiative
  • cf[12345] is the custom field created above
  • Epic Name is a field which is populated on an Epic, but not a Story
• JQL orders the issues by the custom field first.
• Then, where the value is the same (i.e an Epic and all its children), it orders this sub-set of data by the Epic Name. Because only Epics have an Epic Name, they come out on at the top of each sub-set.

If you're not using Advanced Roadmaps - and instead linking Epics to an Initiative via Linked Issues, you could make this JQL work - for example:

"Epic Link" in linkedIssues("ABC-1","relates to") OR issue in linkedIssues("ABC-1","relates to") AND issuetype = Epic ORDER BY cf[12345], "Epic Name"

^ If using Linked Issues, I would advise creating a unique link type for relating Epics to Initiatives, eg. Parent Of/Child Of, and replacing "relates to" above. You can have no link type in there - but this limits the use of Linked Issues for other purposes.

-----------------

Option 2 - Custom Order

The above workaround utilises the Epic's Issue Key - this would put the Epics (and their children) in a sequential order - either Newest > Oldest, or Oldest > Newest depending if you use ASC or DESC.

You could have a more custom order by either:

• Utilising the Custom Field and Rule 2 - and manually entering the custom field's values into the Epics
• Possibly utilising Rank - so that as Rank changes between Epics, so does the order of the results

This is how Rank might work...

Field:

• Create the Custom Field, as above

Rule 1:

• Create a new rule:
  • Trigger: Multiple Issue Events - Issue Created, Issue Updated
  • Condition: Issue Fields Condition - Issue Type (field) equals (condition) Epic (value)
  • Action: Edit Issue - <<Custom Field>> - set value to {{rank}}
• Give the rule a name and publish it

Rule 2:

• Create a new rule:
  • Trigger: Multiple Issue Events - Issue Created, Issue Updated
  • Branch: For "Type of Related Issues", use Stories or Other Issues in Epic. Also Uncheck "Only include issues that have changed..."
    • Action: Edit Issues - <<Custom Field>> - Copy from Epic Issue (available via the 3-dots icon)
  • Branch: For "Type of Related Issues", use Current Issue
    
    • Condition: Advanced Compare Condition - {{issue.Epic Link}} (first value) does not equal (condition) - and leave "second value" Empty
    • Edit Issues: <<Custom Field>> - Copy from Epic Issue (available via the 3-dots icon)
• Give the rule a name and publish it
• Once published, go to Rule Details and check the box under "Allow Rule Trigger" to TRUE - then press Save down the bottom. This allows a change in Rule 1 to trigger Rule 2.

^ Note: The reason branches are needed in this rule is because unlike Issue Key, Rank will change at an Epic level fluidly (Branch 1) and issues will be added to or moved between different Epics (Branch 2).

Ranking:

• Rank the Epics in Advanced Roadmaps (if you have Premium) or...
• Create a new Kanban Board, limit the board to just Epics and drag/drop them into the right order
• For Epics which don't move, you might still need to take the Update actions from Option 1

JQL: 

• Utilise the same JQL functions as Option 1 - it should still work the same way.

-----------------

This should fulfil the need - a few notes:

• Project-Level: As these rules will continually execute against a large number of issues, I would create them at project-level. This is because global rules have a limited number of executions per month, whilst project executions are virtually unlimited.
• Rule 1: If you use Option 1, I would consider changing the Trigger from "Multiple Events" to "Issue Created" for Rule 1 (Epics) once you've done the initial Bulk Change. This will lower the number of rule executions - whilst ensuring each newly created Epic has the copied key. You will need "Multiple Events" though in Option 2

-----------------

There is a lot to digest above - let me know if you have any queries, or if you give one of the two options a go and need help!

Ste