Jira Align- Enterprise Insights: Retrieving Custom Hierarchy details and their associated work items.
Summary
This article explains how to retrieve Custom Hierarchy details and their associated work items using Enterprise Insights in Jira Align.
Environment
Jira Align
Solution
Table Relationships
[current_dw].[Hierarchy] -> Contains all the details of the Parent and Child for each Custom hierarchy level.
[current_dw].[MAP Hierarchy to Epic] -> Contains all the Epic IDs that are associated with the custom hierarchy.
[current_dw].[MAP Hierarchy to Capability] -> Contains all the Capability IDs that are associated with the custom hierarchy.
[current_dw].[MAP Hierarchy to Feature] -> Contains all the Feature IDs that are associated with the custom hierarchy.
Jira Align User Interface
Enterprise Insights results
Query Details
This is a self-join query to handle the hierarchical structure, since both parent and child records are stored in the same table.
The query below returns the hierarchy details for a given Level 4 Custom Hierarchy ID. Please note that in this test scenario, Epics are associated with Level 4.
I recommend further optimizing this query and reviewing it against your specific business requirements, as we aren't SQL experts.
SELECT
L1.[Hierarchy ID] AS 'Level1 CH ID',
L1.[Hierarchy Name] AS 'Level1 Hierarchy Name',
L2.[Hierarchy ID] AS 'Level2 CH ID',
L2.[Hierarchy Name] AS 'Level2 Name',
L3.[Hierarchy ID] AS 'Level3 CH ID',
L3.[Hierarchy Name] AS 'Level3 Name',
L4.[Hierarchy ID] AS 'Level4 CH ID',
L4.[Hierarchy Name] AS 'Level4 Name',
L4.[FK User Owner ID] AS Owner,
L4.[Hierarchy Status] AS Status,
(Select count(*) from [current_dw].[MAP Hierarchy to Epic] where [FK Hierarchy ID]= L4.[Hierarchy ID]) as 'Epics linked'
FROM
[current_dw].[Hierarchy] AS L4
INNER JOIN [current_dw].[Hierarchy] AS L3 ON L3.[Hierarchy ID] = L4.[FK Hierarchy Parent ID]
INNER JOIN [current_dw].[Hierarchy] AS L2 ON L2.[Hierarchy ID] = L3.[FK Hierarchy Parent ID]
INNER JOIN [current_dw].[Hierarchy] AS L1 ON L1.[Hierarchy ID] = L2.[FK Hierarchy Parent ID]
WHERE
L4.[Hierarchy ID] = <Enter_the_Level4_ID_here>;Was this helpful?