Here is one approach, assuming there is a [Sales] measure, a[Clients]
parent-child dimension with an [All] level and an [Employees] dimension
with an [Employee] level. First add a "EmployeeKey" Member Property to
Clients, based on the "responsible_employee_key" column of the dimension
table. Then define 2 calculated measures as follows:

[Measures].[AllEmpSales] as >>
([Measures].[Sales],[Employees].[All Employees])
[Measures].[PerEmpSales] as >>
iif([Employees].CurrentMember.Level
is [Employees].[Employee],
Sum(Filter([Clients].CurrentMember.Children,
[Clients].CurrentMember.Properties("EmployeeKey")
= [Employees].CurrentMember.Properties("KEY")),
[Measures].[AllEmpSales])
+ Sum(Filter([Clients].CurrentMember.Children,
[Clients].CurrentMember.Properties("EmployeeKey")
<> [Employees].CurrentMember.Properties("KEY")),
[Measures].[PerEmpSales]), [Measures].[Sales])
The [Measures].[PerEmpSales] should roll up the sales.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks Deepak.

I thought there would be a way using the DESCENDANT function or dI see things wrong.

Mario

"Deepak Puri" wrote:

Descendants may well work - I used Children and recursion instead,
because I wasn't sure about the rules regarding association of Employees
to Clients. For example, if an Employee was associated to both a client
and some of its descendants, you would presumably only wish to include
sales for the highest client in the hierarchy (the descendants'
contribution would already be rolled up). Of course, this is assuming
Standard Roll-Up on Clients.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Mario,

I tested using a modified Foodmart HR cube, where this worked.
Presumably the [Clients] member selected is [All Clients] when you are
querying, so that the recursion can descend all branches of that
hierarchy. The recursion terminates when a node has no children, or when
the Client EmployeeKey matches that of the selected Employee. Without an
MDX debugger, it's hard to locate the exact failure.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!