Include sales information about sales manager

I need to extract sale informations about every employee in a parentchild dimension, regardless of hierarchy.

The problem is, when an employee is a manager, his sales amount includes aggregated information associated with him (that is his own Sales + sales of the employees reporting to him).
According to BOL, his personal sales amount is stored in DataMember (system-generated member).

Using MDX, how can I extract the "own" sale amount of every employee, whether he is a manager or not.

After a long fight with MDX, I am able to filter out "non-leaf-items", but I am not able to extract the "DataMember" part of nonleaf members.

Any clue is welcome.

To illustrate my problem, below are the numbers shown by "Adventure Works" and the numbers I need to extract.

Browsing Adventure Works cube

Filter: Date.[Calendar Year] = {CY 2004}

Detail Fields: [Measures].[Reseller Sales Amount]

Row Fields: [Employee].[Employees]

Amy E. Alberts (under Ken J. Sanchez and Brian S. Welcker) shows a total sales amount of $4,110,734.65, distributed as follow

Jae B. Pak $1,808,043.26

Rachel B. Valdez $829,512.64

Ranjit R. Varkey Chudukatil $1,374,855.78

total incl. Amy E. Alberts) $4,110,734.65

Amy E. Alberts $98,322.97 (this is not shown in the browser)

I need to extract:

Jae B. Pak $1,808,043.26

Rachel B. Valdez $829,512.64

Ranjit R. Varkey Chudukatil $1,374,855.78

Amy E. Alberts $98,322.97

Thanks.

Ren

Hi

Just a guess, not tested, but how about subtracting the descendant employee's sales if any descendants exist?

Regards

Chris

|||

I looked at many solutions but I am not able to find out the most efficient.

It seems to be an obvious problem and solution is not easy to find.