要約

This article explains how to retrieve Custom Hierarchy details and their associated work items using Enterprise Insights in Jira Align.

環境

Jira Align

ソリューション

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>;