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 ... !!!
Any response to my query....
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.