Hi All,

I don't understand one thing in a query below:

WITH SET [SET1] AS
'ORDER ({[Education Level].[Education Level].Members}, [Gender].[All
Gender].[F], ASC)'
MEMBER [Gender].[RANK1] AS 'rank([Education Level].currentmember, [SET1])'
select
{[Gender].[All Gender].[F], [Gender].[RANK1]} on columns,
{[Education Level].[Education Level].Members} on rows
from Sales
where
([Measures].[Store Sales Net])

This query is ok but if I change WHERE statement for:
([Measures].[Profit])

RANK1 column is filled with values "0,00"
Can somebody give me any explanation, why it is so?

Peter

Looks like a Solve Order issue - [Store Sales Net] is an intrinsic
measure with a lower Solve Order than [Profit], which is a calculated
measure with a Solve Order of 0. Making the Solve Order of [RANK1] > 0
ensures that it is calculated after [Profit], yielding the expected
result:

'ORDER ({[Education Level].[Education Level].Members},
[Gender].[All Gender].[F], ASC)'
MEMBER [Gender].[RANK1] AS 'rank([Education Level].currentmember,
[SET1])',
SOLVE_ORDER = 10
select
{[Gender].[F], [Gender].[RANK1]} on columns,
{[Education Level].[Education Level].Members} on rows
from Sales
where
([Measures].[Profit])

SQL Server 2000 BOL explain this in more detail:

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agmdxnonosfunct_3bzn.asp


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks!


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote