
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:

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:

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:

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:

Click to enlarge
The obvious downside with method is the static nature of the levels.
Happy Coding and Have Fun!