I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing -
- Mark Rolls
--- Lumin Jacs
----- Larry Gomes
------- Messica Owens
------- Tom Ted
----------- Jackson Lopez
----- Matthew Ron
--- Fred jacob
- Jason Ron
--- Jecy Pedro
But beside this parent-child hierarchy I have other attributes like employee address, email and telephone.
My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive).
When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume.
But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy.
How do I get it? The query I used is:
Here Parent Terr ID is parent child hierachy.
[Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&
/* here Mark Rolls is 538018 */
NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members),
NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members)
([Dim Date].[The Year].[The Year].[CY-2010],
View Complete Post