AS2005 MDX-Question (Grouping while ignoring intermediate level)

Hi,

I'm quite new to MDX and try to solve the following problem: given is a dimension having the following hierarchy:

Business Unit A
Sales Area 1Customer XCustomer YCustomer ZSales Area 2Customer VCustomer WCustomer X
Business Unit BSales Area 3......

Now I need the sum of each business units' revenues grouped by customers. In the above example the result should look like:
Business Unit A

Customer VCustomer WCustomer X (sum of Sales Area 1 and Sales Area 2)
Customer YCustomer ZSales Area 2Business Unit BCustomer ...
...
Is this possible with MDX?

Thanx for any help and kind regards,
Gerald

Gerald, try this:

{GENERATE({[OrgDimName].[HierarchyName].[BusinessUnitLevelName].MEMBERS},

{[OrgDimName].[HierarchyName].CURRENTMEMBER,

DESCENDANTS([OrgDimName].[HierarchyName].CURRENTMEMBER,

[OrgDimName].[HierarchyName].[CustomerLevelName], SELF)})} ON ROWS

If you replace SELF with SELF_AND_BEFORE you will also get the Sales Area sub-totals.

HTH

Philip Taylor

|||Philip, thank you for your answer.
What I forgot to mention (and is quite important, I think) is that the dimension is a parent-child-dimension:
business units are on [Level 04]|||

Gerald,

Can you explain the meaning of revenue(X1) and revenue(X2) in relation to Customer X please.

Also you might want to create a level naming template ( see http://msdn2.microsoft.com/en-us/library/ms167115(d=ide).aspx and there is an equivalent in AS2000) which I think makes it easier to navigate your org structure and write MDX queries.

Regards

Philip

|||Philip,

the formula in brackets is just thought as an explanation of how the revenue should be calculated. So the revenue for business unit 1 should be the sum of the revenues of customers V, W, X, Y and Z where customer X appears twice, once with revenue value X1 and once with revenue value X2. In other words "revenue(n)" means the revenue value of customer n stored in the facts. I just see that I've made a typo at customer W: of course it should be revenue(W).

Anyway - I have just had a talk with the guy responsible for the cube design and it looks like we will make a second cube which is optimized for reporting. In this case I'll have a dimension which gives me the desired structure directly and I won't have any problems getting the results our customer would like to see.

thanx and kind regards,
Gerald