Jira Align: How to list the canceled Epics filtered by Portfolio from Enterprise Insights
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article is prepared to help fetch the canceled Epics from Enterprise Insights and Atlassian Analytics.
Atlassian Analytics only the current_dw schema is available. Therefore it is not possible to match the canceled Epics with Portfolio ID since it is also removed.
There is an enhancement request to use the export_dw schema in Atlassian Analytics: JIRAALIGN-6838 - add export_dw schema available in Atlassian Analytics.
Solution
The following SQL query fetches the list of all canceled Epics for the given Portfolio ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE @PortfolioId INT = <Portfolio ID here>
SELECT DISTINCT
eh.[fk epic id],
eh.[epic name],
eh.[canceled flag],
eh.[deleted flag],
eh.[fk program id]
FROM current_dw.[epic history] eh
INNER JOIN (
SELECT DISTINCT
eh_inner.[fk epic id]
FROM current_dw.[epic history] eh_inner
INNER JOIN current_dw.[program] p ON eh_inner.[FK Program ID] = p.[Program ID]
INNER JOIN current_dw.[portfolio] po ON p.[FK Portfolio ID] = po.[Portfolio ID]
WHERE po.[Portfolio ID] = @PortfolioId
) AS EPICS ON eh.[fk epic id] = EPICS.[fk epic id]WHERE eh.[FK Program ID] = 0
AND eh.[Epic Fact Valid To] =
'9999-12-31 00:00:00.000'
AND eh.[canceled flag] =
'Yes'
AND eh.[deleted flag] =
'Yes'Was this helpful?