Help with MDX SCOPE and LINKMEMBER functions

[philippe_g]

Hello All

I am trying to modify how a role-playing dimension reports based on the level of another hierarchy.
Here's an example from AdventureWorks (contrived but it's the closest I can get to my situation):

Say I want values from a measure to be taken from the [Fiscal] hierarchy in the [Date] dimension for the [Employee].[Employee Department].[Department] and [Employee].[Employee Department].[Employee] levels, but I want values from the [Calendar] hierarchy for the (middle) [Employee].[Employee Department].[Title].

I have tried combining SCOPE with Linkmember:

SCOPE ([Employee].[Employee Department].[Title].members);
this = linkmember([Date].[Fiscal].currentmember,[Date].[Calendar]);
end scope;

But that doesn't work (it returns no values), can anybody point me in the right direction?

Thanks

Philippe

[Island1]

Are you attempting to retrieve the data you describe via a report, a browse within Analysis Services, or within a calculated member whose output is then to be presented via some other means?

Could you clarify what the intended final product is supposed to be? I may be able to help you if I can understand the objective.

Thanks.

Bill

[philippe_g]

Thanks for your interest Bill
Users browse the cube directly with Excel, so I don't really have the option of creating separate reports for the different situations. Also I have many measures and even more calculated measures (>120), so creating separate measures for each is again difficult.
Basically the hub of the problem is that at the moment every time users expand or contract the time hierarchy (say on columns), they have to remove the corresponding dimension on rows and replace it with the appropriate one, and I'd like that to be done automatically regardless of the measure being looked at.

Cheers

Are you attempting to retrieve the data you describe via a report, a browse within Analysis Services, or within a calculated member whose output is then to be presented via some other means?

Could you clarify what the intended final product is supposed to be? I may be able to help you if I can understand the objective.

Thanks.

Bill