I have a jira cloud instance with a lot of non-needed information, for example, items open for a long time with no updates, sub-tasks open for closed stories, closed items without resolutions, and many more.
Are there some built-in reports that I can import to achieve that?
what is the best way to check hierarchy inconsistencies with the basic Epic - Story - Sub Task relations?
Hi @guya1976
Welcome to the Community! 
There are no out-of-the-box reports for this; however, in a couple of steps, you can build out these reports.
- For the unresolved issues that have not been updated in a while, you can create a report where you have the Project or the Assignee (or any other) dimension in Rows and define a new calculated measure in the Measures dimension “Unresolved issues last updated 180 days ago or more” with a formula like this:
Sum(
PreviousPeriods(
[Time].[Day].DateMember(
DateAddDays(
Now(),
-180
)
)
),
(
[Measures].[Issues last updated],
[Resolution].[(unresolved)]
)
)
- The following formula will go through the Parent level of the Issue Epic hierarchy and check if the parent is resolved, if yes, then it will compare the number of issues created with issues resolved (count of parent and subtask issues).
Sum(
Filter(
Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Parent]),
NOT IsEmpty([Issue.Epic].CurrentMember.Get('Resolved at'))
),
CASE WHEN
[Measures].[Issues created] > [Measures].[Issues resolved]
THEN
1
END
)
- For closed items without resolution, you can first set up the Issues closed measure in the import options of the Jira source (Source Data tab → “Edit” Jira source): Issues closed measure
When you’ve added which statuses should be considered as closed, run the import and create a measure with the following formula:
Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
IsEmpty([Issue].CurrentMember.Get('Resolved at'))
),
CASE WHEN
[Measures].[Issues closed] > 0
THEN
1
END
)
Could you please share a bit more details about the possible inconsistencies in the Epic - Story - Sub-task hierarchy? What should the measure check for?
​Best regards,
​Nauris
1 Like
Hi Nuaris, thank you for your reply!
to be more specific these are some of the reports I need which I was able to use extended search plugins to achieve and want to do in EazyBI:
- open issues for closed initiatives
- closed initiatives with open Epics
- closed Epics with open issues
- open subtasks with closed parents
- closed issues with empty resolution
- closed issues with open blocking linked issues
- initiatives in progress with no Epics
In general, I want to ensure no inconsistencies in the hierarchy.
it’s probably worth mentioning that our Jira issue hierarchy is as follows:
- company initiative => Initiative => Epic …
thanks again for your support!
Guy
Hi @guya1976
Happy to help!
Have you already imported this hierarchy into eazyBI?
If not, please follow this documentation page and community post to do so:
https://docs.eazybi.com/eazybi/data-import/data-from-jira/advanced-data-import-options/additional-issue-hierarchies
When you’ve set up the hierarchy, you can use the Issue dimension with the Initiative level selected in the Rows section and use a similar formula as previously to find open issues in resolved initiatives:
Sum(
Filter(
Descendants([Issue.HierarchyName].CurrentMember, [Issue.HierarchyName].[Initiative]),
NOT IsEmpty([Issue.HierarchyName].CurrentMember.Get('Resolved at'))
),
CASE WHEN
[Measures].[Issues created] > [Measures].[Issues resolved]
THEN
1
END
)
Replace the HierarchyName with the name you’ve specified in the Advanced settings when setting up this hierarchy.
To do the same check with Epic level select in Rows, simply change the level that the formula is pointing towards:
Sum(
Filter(
Descendants([Issue.HierarchyName].CurrentMember, [Issue.HierarchyName].[Epic]),
NOT IsEmpty([Issue.HierarchyName].CurrentMember.Get('Resolved at'))
),
CASE WHEN
[Measures].[Issues created] > [Measures].[Issues resolved]
THEN
1
END
)
For closed issues with empty resolutions, use the last formula from my previous reply.
Let me know once you have this set up, and we’ll continue with the next metrics.
​Best regards,
​Nauris
Hi Nauris, thank you! works like a charm 
Looking forward to the next metrics.
thanks,
Guy
Hi @guya1976
Nauris has gone on a well-deserved vacation 
so I’ll take over this use case
To find closed issues with open blocking linked issues, start with issue link import. I suggest importing the issue links at the account level (through import options—> Custom fields), as they are more user-friendly. See the instructions here:
When you have imported the issue links, you can build your report by defining a new calculated measure with the formula below, replacing “Linked issues” with the name you will choose when you import your linked issues.
NonZero(
Sum(
Filter(
DescendantsSet([Linked Issues].Currentmember, [Linked Issues].[Linked Issues]),
IsEmpty([Linked Issues].CurrentHierarchyMember.get('Resolved at'))
),
[Measures].[Issues closed]
)
)
To find Initiatives in progress with no Epics, define a new calculated measure with formula below
Sum(
Filter(
Descendants([Issue.HierarchyName].CurrentMember, [Issue.HierarchyName].[Initiative]),
[Measures].[Issue status] = 'In Progress'
AND
IsEmpty(
(
[Measures].[Issues created],
[Issue Type].[Epic]
)
)
),
[Measures].[Issues created]
)
Let me know if there are some other calculations you are still missing!
Best wishes,
Elita from support@eazybi.com
1 Like