Is there some one that knows why this syntax is not giving the correct answer:

suM(NonEmptyCrossjoin(Descendants([Test_Tijd].CurrentMember,1,Leaves),Descendants([Test_Prod].CurrentMember,1,Leaves),Descendants([Status].CurrentMember,1,Leaves),([Measures].[Prijs])*([Measures].[Volume])))

Later on I what to use it for price variance calculation.

I think the problem that you're running into is that Analysis Services is
summing up the values of each the measures first and then taking the
product. What you need to do is have a column in your fact table that takes
the product of the price and volume. You can do this by creating a view
that adds the calculation or creating a new measure and setting the source
column to be equal to the product of those two columns. There's an exmaple
of this in the foodmart 2000 sales cube (I believe it's the store sales net
measure) if you need help with the syntax.

Whether you go with the view approach or just modifying the source column
for the measure, in the end you'll have a new measure in the cube based on
teh SUM aggregate type. That way Analysis Services will take the product
before it rolls up the values using the sum.

Sean


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.



"Christiaan" <anonymous (AT) discussions (DOT) microsoft.com> wrote

dants([Test_Prod].CurrentMember,1,Leaves),Descendants([Status].CurrentMember
,1,Leaves),([Measures].[Prijs])*([Measures].[Volume])))

NonEmptyCrossJoin does not work with calculated members.

"Christiaan" <anonymous (AT) discussions (DOT) microsoft.com> wrote

dants([Test_Prod].CurrentMember,1,Leaves),Descendants([Status].CurrentMember
,1,Leaves),([Measures].[Prijs])*([Measures].[Volume])))

A couple of comments:

- why has the distance of descendants been limited to 1?

- it appears that parenthesis grouping is incorrect

Based on the above, here is the modified syntax (of course, Sean's
suggestion will give better performance):


cendants([Test_Prod].CurrentMember,,Leaves),Descendants([Status].Current
Member,,Leaves)),([Measures].[Prijs])*([Measures].[Volume]))

- Deepak

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