Rank order in Yukon opposite of that with AS2K!

[Philip]

Hi,

The following AS2K query returns the Top 10 in descending order of
Sales and the rank starts at 1 and ends at 10:

With
Set [Top10] As
'{TOPCOUNT([Product].[Product].members,10,[Measures].[Sales Amount])}'
Member [Measures].[Rank] As
'RANK([Product].CurrentMember,{[Top10]},[Measures].[Sales Amount])'
Select
{[Measures].[Sales Amount],[Measures].[Rank]} On Columns ,
{[Top10]} On Rows
>From [SalesSummary]
WHERE ( [Time].[All Time].[2004] )

Sales Amount Rank
Mountain-200 Black, 46 $635,712.23 1
Mountain-200 Silver, 46 $596,237.43 2
Mountain-200 Silver, 42 $566,077.56 3
Mountain-200 Silver, 38 $556,797.60 4
Mountain-200 Black, 38 $555,387.58 5
Mountain-200 Black, 42 $518,667.74 6
Road-350-W Yellow, 40 $246,643.55 7
Touring-1000 Yellow, 54 $224,102.58 8
Road-350-W Yellow, 42 $219,427.71 9
Road-350-W Yellow, 48 $209,221.77 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):

With
Set [Top10] As
'{TOPCOUNT([Product].[Product].[Product].members,10,[Measures].[Internet
Sales Amount])}'
Member [Measures].[Rank] As
'RANK([Product].[Product].CurrentMember,{[Top10]},[Measures].[Internet
Sales Amount])'
Select
{[Measures].[Internet Sales Amount],[Measures].[Rank]} On Columns ,
{[Top10]} On Rows
>From [Adventure Works]
WHERE ( [Date].[Fiscal].[Fiscal Year].&[2004] )

Sales Amount Rank
Mountain-200 Black, 46 $635,712.23 10
Mountain-200 Silver, 46 $596,237.43 9
Mountain-200 Silver, 42 $566,077.56 8
Mountain-200 Silver, 38 $556,797.60 7
Mountain-200 Black, 38 $555,387.58 6
Mountain-200 Black, 42 $518,667.74 5
Road-350-W Yellow, 40 $246,643.55 4
Touring-1000 Yellow, 54 $224,102.58 3
Road-350-W Yellow, 42 $219,427.71 2
Road-350-W Yellow, 48 $209,221.77 1

How can I get a the same rank with Yukon as I got with AS2K?

Thanks,

[in...@exologic.com]

Phillip,

I would think that wrapping that set in an Order would do the trick, a
la:
'Order (Rank ([Product].CurrentMember,{[Top10]},[Measures].[Sales
Amount]) , ... )'

Of course the ASC and DESC should flip the direction. Hopefully
they'll change the behavior back to AS2K-style, but maybe there is an
intentional reason they've done this.

Good luck.

- ExoLogic Team

[in...@exologic.com]

Philip,

Here is something that I tested, but only on 2000, but the syntax
should be good for 2005 and try toggling the ASC and DESC:

Note that this is for a different cube to yours, but it is similar:

With
Set [Top10] As
'{TOPCOUNT([Product].Members, 10, [Measures].[Measure])}'

Member [Measures].[Rank] As

'RANK([Product].CurrentMember,{[Top10]},[Measures].[Measure])'
Select
{[Measures].[Measure],[Measures].[Rank]} On Columns ,
{Order({[Top10]},([Measures].[Measure]), ASC)} On Rows
>From [Basic]

- ExoLogic Team

[in...@exologic.com]

After further review (ahem), the ASC and DESC did not work properly,
but the BASC and BDESC did.

Let us know if that works out.

- ExoLogic Team

[Deepak Puri]

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.

But in the example MDX query (where there are no duplicate Sales
Amount), the 2-parameter version of Rank should work consistently in AS
2005 as well:

>>
With
Set [Top10] As
'{TOPCOUNT([Product].[Product].[Product].members,10,[Measures].[Internet
Sales Amount])}'
Member [Measures].[Rank] As

'RANK([Product].[Product].CurrentMember,{[Top10]})'

Select
{[Measures].[Internet Sales Amount],[Measures].[Rank]} On Columns ,
{[Top10]} On Rows
From [Adventure Works]
WHERE ( [Date].[Fiscal].[Fiscal Year].&[2004] )
>>

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

[Philip]

Hi,

Yes, you're right. SQL2005 seems to sort the set given in parameter 2
if you specify the 3rd parameter. The solution that works for me is
simply not to specify the 3rd parameter.

Thanks.

Philip