Analytics Software

Hi All,

I am in OBIEE 11G v6. I have a sales fact table where the grain is one sale. So I have one row for every sale done.

Now I have a ragged employee hierarchy like this with David at the root node.
David >>Richard>>Sean
David >>James

Also, I have a role dimension which gives me what role each employee has performed on a sale. Only one employee can be associated with one sale. This is the way Roles have been asssigned
David = Manager
Richard = Off1
Sean = Off2
James = Off2
Both Sean and James can have same Roles. Now I have created a parent child hierarchy for my employee dimension and the closure table. Defined the member key, ancestor key relationship in the parent child setting etc.

Now in the report when I pull the parent child hierarchy and the sales_amount in the report, it comes out perfect with all the ragged hierarchy resolved. But the issue comes when I try to limit the report on Role = Off2. It gives me an error saying " The layout of this view combined with the data, selection , drills resulted in no data. Undo drill and view prompt values". Basically what i want is to be able to select any role type and then my hierarchy should be adjusted to show me that data. Like when I select Off2, I want to see David at the Top level and Sean and James under him because they are both Off 2 and David is their manager.

Is that possbile? Also, am I getting this error because when I select Off2 though it gets Sean and James but since David is not Off2, I don't get the data?

I hope I was able to explain the issue, any help on this would be greatly appreciated.

Thanks
Ronny