Register today for Oracle CloudWorld. October 17th - 20th, Las Vegas

Register now

Hillel Cooperman, Senior VP of User Experience Design, has a message for you on Oracle CloudWorld

Watch now
BIP Report - Department Hierarchy — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

BIP Report - Department Hierarchy

Received Response


Hello, can I create a SQL for department hierarchy out of this sample pattern? Does it work in Oracle BI Publisher?


 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)

Howdy, Stranger!

Log In

To view full details, sign in.


Don't have an account? Click here to get started!