BIP Report - Department Hierarchy
Summary:
Hello, can I create a SQL for department hierarchy out of this sample pattern? Does it work in Oracle BI Publisher?
with
Org ( id,idparent, name, level , Path) as (
SELECT ID , Parentid, Name, convert(int, [Level]) as l , convert(nvarchar(max), '<x>' + name + '</x>')
FROM [HRR].[dbo].OHCM_OrgChart
where Parentid is null
union all
select o.ID , o.Parentid, o.Name , CONVERT(int, cte.level) + 1 , CONVERT(nvarchar(max), Path + '<x>' + replace(o.Name, '&', '-') + '</x>')
FROM [HRR].[dbo].[OHCM_OrgChart] as o
inner join Org as cte on cte.ID = o.Parentid)
Tagged:
0