MS SQL Server OLAP - MDX Rank problem

Author

MDX Rank problem

2005-04-26, 7:23 am

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

Deepak Puri

2005-04-26, 8:24 pm

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:
[color=darkred]
WITH SET [SET1] AS
'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])[color=dark
red]

SQL Server 2000 BOL explain this in more detail:

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

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

2005-04-28, 3:24 am

Thanks!

"Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
news:uhfv8npSFHA.3548@TK2MSFTNGP10.phx.gbl...
> 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:
>
> WITH SET [SET1] AS
> '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/d...y/en-us/olapdma
> d/ agmdxnonosfunct_3bzn
.asp
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***

Sponsored Links