I went through several options on making Location Hierarchy Reports, and found these queries to be quite beneficial. The code is different, depending on the backend being Oracle or Microsoft.<\/p>\n
select level, lpad (‘ ‘, 8 * (level – 1)) || lh.location “LOCATION”, l.description, lh.parent, lh.siteid<\/span> select lo.location, lh.parent, lo.description<\/span> WITH Hierarchy<\/span> I went through several options on making Location Hierarchy Reports, and found these queries to be quite beneficial. The code is different, depending on the backend being Oracle or Microsoft. Oracle select level, lpad (‘ ‘, 8 * (level – … Continue reading
\n from lochierarchy lh, locations l<\/span>
\n where lh.location = l.location and lh.siteid = l.siteid and lh.siteid = ‘TXPANHANDLE’<\/span>
\n start with lh.location =’CANADIAN’<\/span>
\n connect by prior lh.location = lh.parent<\/span><\/p>\nMicrosoft<\/h4>\n
\n into #tempo<\/span>
\n from locations lo<\/span>
\n inner join lochierarchy lh on lh.location = lo.location<\/span>
\n where lo.status in (‘OPERATING’, ‘ACTIVE’) and lh.parent not in (‘DELETE’);<\/span><\/p>\n
\n AS ( SELECT parent AS RootID ,<\/span>
\n 1 AS [Level] ,<\/span>
\n ‘\\\\’ + CAST(parent AS VARCHAR(MAX)) + ‘\\’ + CAST(location AS VARCHAR(MAX))AS [Path] ,<\/span>
\n parent ,<\/span>
\n location<\/span>
\n FROM #tempo<\/span>
\n WHERE NOT parent IN ( SELECT location<\/span>
\n FROM #tempo )<\/span>
\n UNION ALL<\/span>
\n SELECT P.RootID ,<\/span>
\n P.[Level] + 1 ,<\/span>
\n P.[Path] + ‘\\’ + CAST(C.location AS VARCHAR(MAX)) ,<\/span>
\n C.parent ,<\/span>
\n C.location<\/span>
\n FROM Hierarchy P<\/span>
\n INNER JOIN #tempo C ON P.location = C.parent<\/span>
\n )<\/span>
\n SELECT case level<\/span>
\n when 1 then ”<\/span>
\n when 2 then ‘ |–‘<\/span>
\n when 3 then ‘ | |–‘<\/span>
\n when 4 then ‘ | | |–‘<\/span>
\n when 5 then ‘ | | | |–‘<\/span>
\n when 6 then ‘ | | | | |–‘<\/span>
\n when 7 then ‘ | | | | | |–‘<\/span>
\n when 8 then ‘ | | | | | | |–‘<\/span>
\n when 9 then ‘ | | | | | | | |–‘<\/span>
\n when 10 then ‘ | | | | | | | | |–‘<\/span>
\n else ‘XXX’<\/span>
\n end<\/span>
\n + location ‘Location Hierarchy’<\/span>
\n FROM Hierarchy<\/span>
\n ORDER BY [Path] ;<\/span>
\n drop table #tempo;<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"