I tested Rank functions. I got the expected results from 3rd and 4th sample.

But, I got the same results from 1st and 2nd sample and the 1st one had
unexpected behavior.

Any information on this behavior?

1)
WITH MEMBER Measures.RankA AS
'RANK([Date].[Calendar Year].CurrentMember,
ORDER([Date].[Calendar Year].[Calendar Year].Members,
[Measures].[Internet Sales Amount], BDESC),[Measures].[Internet Sales
Amount])'
Select {[Measures].[Internet Sales Amount], Measures.RankA} ON COLUMNS,
ORDER([Date].[Calendar Year].[Calendar Year].Members,[Measures].[Internet
Sales Amount], DESC) ON ROWS
FROM [Adventure Works]
go

2)
WITH MEMBER Measures.RankA AS
'RANK([Date].[Calendar Year].CurrentMember,
ORDER([Date].[Calendar Year].[Calendar Year].Members,
[Measures].[Internet Sales Amount], BASC),[Measures].[Internet Sales
Amount])'
Select {[Measures].[Internet Sales Amount], Measures.RankA} ON COLUMNS,
ORDER([Date].[Calendar Year].[Calendar Year].Members,[Measures].[Internet
Sales Amount], DESC) ON ROWS
FROM [Adventure Works]
go

3)
WITH MEMBER Measures.RankA AS
'RANK([Date].[Calendar Year].CurrentMember,
ORDER([Date].[Calendar Year].[Calendar Year].Members,
[Measures].[Internet Sales Amount], DESC))'
Select {[Measures].[Internet Sales Amount], Measures.RankA} ON COLUMNS,
ORDER([Date].[Calendar Year].[Calendar Year].Members,[Measures].[Internet
Sales Amount], DESC) ON ROWS
FROM [Adventure Works]
go

4)
WITH MEMBER Measures.RankA AS
'RANK([Date].[Calendar Year].CurrentMember,
ORDER([Date].[Calendar Year].[Calendar Year].Members,
[Measures].[Internet Sales Amount], ASC))'
Select {[Measures].[Internet Sales Amount], Measures.RankA} ON COLUMNS,
ORDER([Date].[Calendar Year].[Calendar Year].Members,[Measures].[Internet
Sales Amount], DESC) ON ROWS
FROM [Adventure Works]
go

Thanks in advance,

Ohjoo Kwon

Hi Ohjoo,

This behavior of Rank (which seems to contradict SQL Server 2005 BOL and
AS 2000) has been noted in a couple of posts here, starting with the
Sept CTP. Maybe someone from Microsoft could comment on whether this
changed by design?


http://groups.google.com/group/micro...olap/msg/4b8c0
6c0fc689ac9
From: "alanr"
Date: 13 Oct 2005 09:06:25 -0700
Subject: visual rank calculated member on server is very very slow

Greetings!

I am struggling with the performance of a visual ranking calculation
defined on the server. I based this on posts from Tom Chester and
others. I am using the September CTP (which as a side note seems to
have eliminated the need to do order the set before ranking?).
...

http://groups.google.com/group/micro...olap/msg/9495b
d8faff09fe6
From: "Philip"
Date: 13 Oct 2005 12:44:08 -0700
Subject: Rank order in Yukon opposite of that with AS2K!

Hi,

The following AS2K query returns the Top 10 in descending order of
Sales and the rank starts at 1 and ends at 10:
...
However the equivalent query in Yukon returns the same Top 10 members
in the same order but the rank starts and 10 and goes down to 1 (which
is wrong):
...
From: Deepak Puri
Date: Thu, 13 Oct 2005 22:14:43 -0700
Subject: Re: Rank order in Yukon opposite of that with AS2K!

Maybe an earlier post by Alan provide a clue, when he observed that "I
am using the September CTP (which as a side note seems to
have eliminated the need to do order the set before ranking?)". So AS
2005 seems to do an ascending ranking.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak, Philip,

Unfortunately, it ranks in reverse order - this will be fixed in sp1. This
is not good at all - after sp1 is installed, your calculations will revert
to AS2000 behavior.

Regards,
Richard

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.

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