Displaying Dynamic Level Hierarchy on Report

[sidd_b]

Hello All ,

I am doing a prototype where I need to display a dynamic hierarchy on report (organization hierarchy whose levels are not fixed). 
For Example : At one point of time/during 1st load Input may come like :

1. John (CEO)
2) Mike (VP) -> Reporting to -> John (CEO)
3) Victor (VP) -> Reporting to -> John (CEO)
4) Karla (AVP) -> Reporting to -> Mike (VP)
5) Ross (Executive) -> Reporting to -> Karla (AVP)

Total levels : 4 (CEO , VP , AVP , Executive)

At another point of time or During 2nd Load the Data might be :

1) John (CEO)
2) Mike (VP) -> Reporting to -> John (CEO)
3) Victor (VP) -> Reporting to -> John (CEO)
4) Karla (AVP) -> Reporting to -> Mike (VP)
5) Ross (Executive) -> Reporting to -> Karla (AVP)
6) Maya (Intern) -> Reporting to -> Ross (Executive)
Total levels : 5 (CEO , VP , AVP , Executive, Intern)

The expected report layout can be anything List , Crosstab .Initially I have decided to show information something like as follows :
For 1st load user can see the data like :

CEO         VP               AVP               Executive 

                MIKE            KARLA           ROSS
JOHN   
               VICTOR 

For 2nd Load :

For 1st load user can see the data like :
CEO         VP               AVP               EXECUTIVE       INTERN
                MIKE            KARLA           ROSS               MAYA
JOHN   
               VICTOR 


I have tried multiple options on report side as well as in framework model (using dimentional model) but I m not able to decide the database structure which will support this dynamic level requirement . Another challenge is how to show it on report I have tried Crosstab, Repeter with master Details  as well as List to get the Output but hard luck.

Even I have tried google charts(https://developers.google.com/chart/interactive/docs/gallery/orgchart ) but due to security constrains we can not push any of our data to these charts.

The Good thing about this prototype is we have free to choose database schema as well as report layout and one more thing user dont want to see any fact data. user is just interested in seeing the hierarchy .

          It would be  really helpful if someone will give me the pointers . Thanks in Advance ... !!!

[sidd_b]

Any response to my query....

[bdbits]

Off the top of my head, very problem-specific, probably sub-optimal, and definitely not a general HR model, but your database might have something like this.

* A dim table for the positions, let's call it Dim_OrgTree.
    Dim_OrgTree_Key
    Level 1
    Level 2
    Level 3
    Level 4
    Level 5

* A dim table for the employees, let's call it Dim_Emp.
    Dim_Emp_Key
    Emp_Name

* A "factless fact" table, let's call it Emp_Fact.
    Dim_Emp_Key
    Dim_OrgTree_Key
    Emp_Count

Rows in Dim_OrgTree (ragged hierarchy):
    1, CEO
    2, CEO, VP
    3, CEO, VP, AVP
    4, CEO, VP, AVP, Executive
    5, CEO, VP, AVP, Executive, Intern

Rows in Dim_Emp:
    1, John
    2, Mike
    3, Victor
    4, Karla
    5, Ross
    6, Maya

Rows in Emp_Fact:
    1, 1, 1
    2, 2, 1
    3, 2, 1
    4, 3, 1
    5, 4, 1
    6, 5, 1

I think you can see how this would be modeled dimensionally in FM. You have the fact table to tie things together, and the Emp_Count column can be used in calculations if needed, but you do not have to expose it to the users.

I think the reports should be pretty straightforward with a model based on the above.