Flattening a Parent Child Hierarchy with EnumeratedPath

HFM_Hierarchy

This is for companies with HFM repository on MS SQL or users wanting to flattening a parent-child hierarchy. If you are administratoring HFM and have users who would like to have that HFM Account Hierarchy in SQL. These scripts will help you with flattening a parent-child hierarchy for internal reporting. They are designed and can be very useful for both SQL and HFM. By using a recursive CTE we can enumerate the path from the leaf to the root/roof (Bottom to the top). See the first image to envision the HFM parent child hierarchy tree.

First I created a temp table to work with one item’s parent-child hierarchy with 14 levels. The -1 is the top of the house  itemid in HFM; other applications/ examples may have NULL for the top parentid. Once you have this working you will want to change the first SQL view below to point to the hfm.dbo.Company_Account_Layout table.

SELECT * INTO tmp_GridResults_1
FROM (
SELECT N'860' AS [ItemID], N'861' AS [ParentID] UNION ALL
SELECT N'861' AS [ItemID], N'868' AS [ParentID] UNION ALL
SELECT N'868' AS [ItemID], N'908' AS [ParentID] UNION ALL
SELECT N'908' AS [ItemID], N'1056' AS [ParentID] UNION ALL
SELECT N'1056' AS [ItemID], N'1704' AS [ParentID] UNION ALL
SELECT N'1704' AS [ItemID], N'1706' AS [ParentID] UNION ALL
SELECT N'1706' AS [ItemID], N'1371' AS [ParentID] UNION ALL
SELECT N'1371' AS [ItemID], N'1708' AS [ParentID] UNION ALL
SELECT N'1708' AS [ItemID], N'1709' AS [ParentID] UNION ALL
SELECT N'1709' AS [ItemID], N'1711' AS [ParentID] UNION ALL
SELECT N'1711' AS [ItemID], N'1903' AS [ParentID] UNION ALL
SELECT N'1903' AS [ItemID], N'2104' AS [ParentID] UNION ALL
SELECT N'2104' AS [ItemID], N'-1' AS [ParentID] ) t;
SELECT [ItemID], [ParentID]
FROM tmp_GridResults_1

This produces:

HFM1

Click to enlarge

In this cte I’m going to build the tree Path, ItemID, ParentID and the Level. Remember change the hfm.dbo.Company_Account_Layout table after you have the temp table working.

CREATE VIEW [dbo].[vw_HFM_CTE] AS

WITH cte([ItemID] ,[Path],[Level], [ParentID])
AS
(
--Parents
SELECT e.[ItemID],
Path=CAST(CAST(e.[ItemID] AS VARCHAR(500)) + ',' AS VARCHAR(1000)),
0 AS [Level],
[ParentID]
FROM [dbo].[tmp_GridResults_1]AS e
--FROM hfm.dbo.Company_Account_Layout AS e
WHERE e.[ParentID] = -1
UNION ALL
--Levels up
SELECT
l.[ItemID],
Path1=CAST(CAST(cte.Path AS VARCHAR(500)) + CAST(l.[ItemID] AS VARCHAR(500)) + ',' AS VARCHAR(1000)),
[Level] + 1,
l.[ParentID] AS [ParentID]
FROM [dbo].[tmp_GridResults_1] AS l
--FROM hfm.dbo.Company_Account_Layout AS l
INNER JOIN cte
ON l.[ParentID] = cte.[ItemID]
)
SELECT *
FROM cte
GO

This produces:

HFM2

Click to enlarge

Then to keep things easy and clear for me I break into another view. I built a SQL view to flattening a parent-child hierarchy path with another cte that converts the Path column to XML and then cross apply to unpivot the data then I pivot the data back on the levels.

I’m including HFM’s Label and Description from the COMPANY_ACCOUNT_ITEM and COMPANY_ACCOUNT_DESC tables.

The three case statements are separating the label, description, and child item. I also replace brackets around the work [NONE] in our labels.

CREATE VIEW [dbo].[vw_HFM_CTE_Results] AS
 WITH cte1 AS (

 SELECT
 t2.ItemID,
 t3.split,
 ROW_NUMBER() OVER (PARTITION BY t2.ItemID ORDER BY t2.ItemID) AS num
 FROM
 (
 SELECT *,
 CAST('<X>'+REPLACE(t.path,',','</X><X>')+'</X>' AS XML) AS xmlfilter
 FROM [HFM].[dbo].[vw_HFM_CTE] t
 WHERE [Path] NOT LIKE '%,2101,%'
 ) t2
 CROSS APPLY
 (
 SELECT col1data.D.value('.','varchar(50)') AS split
 FROM t2.xmlfilter.nodes('X') AS col1data(D)) t3

 )

SELECT ItemID
 ,[1] AS [1_Path]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([1],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([1],'[',''),']',''))) END AS [1]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([1],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([1],'[',''),']',''))+1,CHARINDEX('|',REVERSE(REPLACE(REPLACE([1],'[',''),']','')))-1)
 END AS [1_Label]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([1],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([1],'[',''),']','')))))
 END AS [1_Description]
 ------
 ,[2] AS [2_Path]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([2],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']',''))) END AS [2]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([2],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']',''))+1,LEN([2])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([2],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']','')))
 END AS [2_Label]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([2],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([2],'[',''),']','')))))
 END AS [2_Description]
 ------
 ,[3] AS [3_Path]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([3],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']',''))) END AS [3]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([3],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']',''))+1,LEN([3])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([3],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']','')))
 END AS [3_Label]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([3],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([3],'[',''),']','')))))
 END AS [3_Description]
 --------
 ,[4] AS [4_Path]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([4],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']',''))) END AS [4]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([4],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']',''))+1,LEN([4])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([4],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']','')))
 END AS [4_Label]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([4],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([4],'[',''),']','')))))
 END AS [4_Description]
 -------
 ,[5] AS [5_Path]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([5],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']',''))) END AS [5]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([5],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']',''))+1,LEN([5])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([5],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']','')))
 END AS [5_Label]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([5],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([5],'[',''),']','')))))
 END AS [5_Description]
 -------
 ,[6] AS [6_Path]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([6],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']',''))) END AS [6]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([6],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']',''))+1,LEN([6])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([6],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']','')))
 END AS [6_Label]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([6],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([6],'[',''),']','')))))
 END AS [6_Description]
 -------
 ,[7] AS [7_Path]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([7],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']',''))) END AS [7]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([7],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']',''))+1,LEN([7])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([7],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']','')))
 END AS [7_Label]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([7],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([7],'[',''),']','')))))
 END AS [7_Description]
 -------
 ,[8] AS [8_Path]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([8],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']',''))) END AS [8]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([8],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']',''))+1,LEN([8])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([8],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']','')))
 END AS [8_Label]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([8],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([8],'[',''),']','')))))
 END AS [8_Description]
 -------
 ,[9] AS [9_Path]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([9],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']',''))) END AS [9]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([9],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']',''))+1,LEN([9])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([9],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']','')))
 END AS [9_Label]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([9],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([9],'[',''),']','')))))
 END AS [9_Description]
 -------
 ,[10] AS [10_Path]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([10],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']',''))) END AS [10]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([10],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']',''))+1,LEN([10])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([10],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']','')))
 END AS [10_Label]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([10],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([10],'[',''),']','')))))
 END AS [10_Description]
 -------
 ,[11] AS [11_Path]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([11],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']',''))) END AS [11]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([11],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']',''))+1,LEN([11])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([11],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']','')))
 END AS [11_Label]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([11],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([11],'[',''),']','')))))
 END AS [11_Description]
 -------
 ,[12] AS [12_Path]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([12],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']',''))) END AS [12]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([12],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']',''))+1,LEN([12])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([12],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']','')))
 END AS [12_Label]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([12],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([12],'[',''),']','')))))
 END AS [12_Description]
 -------
 ,[13] AS [13_Path]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([13],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']',''))) END AS [13]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([13],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']',''))+1,LEN([13])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([13],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']','')))
 END AS [13_Label]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([13],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([13],'[',''),']','')))))
 END AS [13_Description]
 -------
 ,[14] AS [14_Path]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([14],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']',''))) END AS [14]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([14],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']',''))+1,LEN([14])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([14],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']','')))
 END AS [14_Label]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([14],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([14],'[',''),']','')))))
 END AS [14_Description]
 -------
FROM
(
 SELECT cte1.ItemID, num, CAST(split+'|'+CAST(LTRIM(RTRIM(t4.Label))AS VARCHAR(20))+'|'+LTRIM(RTRIM(t5.[Description]))AS VARCHAR(8000)) AS split
 FROM CTE1
 INNER JOIN hfm.dbo.COMPANY_ACCOUNT_ITEM t4
 ON cte1.split = t4.ItemID

 INNER JOIN hfm.dbo.COMPANY_ACCOUNT_DESC t5</pre>
<pre> ON cte1.split = t5.ItemID
) AS sourcetable
PIVOT
(MAX(split) FOR num IN ([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12],[13],[14])
) AS pivottable
GO

This produces:

HFM3

Click to enlarge

Then I have a 4th view to filter alternate hierarchy paths and comment out the paths.

CREATE VIEW [dbo].[vw_HFM_Hierarchy]
AS
SELECT cte.[ItemID]
,t1.Label
 --,[1_Path]
 ,[1]
 ,[1_Label]
 ,[1_Description]
 --,[2_Path]
 ,[2]
 ,[2_Label]
 ,[2_Description]
 --,[3_Path]
 ,[3]
 ,[3_Label]
 ,[3_Description]
 --,[4_Path]
 ,[4]
 ,[4_Label]
 ,[4_Description]
 --,[5_Path]
 ,[5]
 ,[5_Label]
 ,[5_Description]
 -- ,[6_Path]
 ,[6]
 ,[6_Label]
 ,[6_Description]
 --,[7_Path]
 ,[7]
 ,[7_Label]
 ,[7_Description]
 -- ,[8_Path]
 ,[8]
 ,[8_Label]
 ,[8_Description]
 --,[9_Path]
 ,[9]
 ,[9_Label]
 ,[9_Description]
 -- ,[10_Path]
 ,[10]
 ,[10_Label]
 ,[10_Description]
 --,[11_Path]
 ,[11]
 ,[11_Label]
 ,[11_Description]
 -- ,[12_Path]
 ,[12]
 ,[12_Label]
 ,[12_Description]
 -- ,[13_Path]
 ,[13]
 ,[13_Label]
 ,[13_Description]
 --,[14_Path]
 ,[14]
 ,[14_Label]
 ,[14_Description]
 FROM [HFM].[dbo].[vw_HFM_CTE_Results] cte

 LEFT JOIN [hfm].[dbo].[COMPANY_ACCOUNT_ITEM] t1
 ON t1.ItemID = cte.ItemID

WHERE [FirstChildID] = -1
AND LEN([Label]) = 4
GO

This produces:

HFM4

Click to enlarge

The obvious downside with method is the static nature of the levels.

Happy Coding and Have Fun!

Advertisements