I am struggling to find the solution for my requirement. I have posted it earlier but i didnt give complete scenario so reposting it. We want to track the employee attendence details.
I have a table which provides the Hierarchy relationship.
Empno ParentEmpno Level parentlevel
1000 Level1
1001 1000 Level 2 Level1
And the i have two fact tables.One for his Office in Time and other has out of office (Out of Office is not only his leave but also contains On Duty for govt or exam invegilation for Govt schools etc).
And these details are entered by mostly Level 1 and Level 2 employees. Not the other levels.
Now my requirement is to show each employee 1. how much time they are in office and how much time they are not in office. i want to show in shuch a way
level 1 will show his own data. Level 2 will show his data(if he has )+ Level 1 data .And level 3 will show the sum(Level 3) +level 2 +Level 2. It is parent child hierarchy.
If i click on the parent which is not having the fact but it should show the sum of its child .
Let me know if any clarifications required.
I tried the staright table with running total it is not working(i tried without preaggregation in the load script). Since staright table or pivot table will show only if it has the fact. Since the parent may or may not have fact i need to sum the child and show it in the parent.
I am fine with preaggregation also. but i need the result. it would be really helpful if some body throw some lime light on it.
