I have read some of the examples posted to the group about multiple
order() calls, but I am still stumped (only been writing MDX for a
couple weeks). I tried the nested order() calls and indeed that method
does not work. I am wondering of anyone can point me in the right
direction.

Ultimately I need to order this set ascending alphabetically by Product
Family and descending Final Z Code by Cust Ret Last Week within the
Product Family. I also need to rank() the Final Z Codes([Attributed
Class].[All Attributed Class].[WDF].children) ascending.

I am including what MDX I have created below. So far it returns the
desired set, just not ordered and with the rank:

select
{[Measures].[Cust Ret Last Week]} on columns,
nonemptycrossjoin({[Product Family].[All Product Family].children},
{topcount([Attributed Class].[All
Attributed Class].[WDF].children, 40, [Measures].[Cust Ret Last
Week])}) on rows
from
[MyCube]
where
([Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid])

Desired Result:

Product Family | Final Z Code | Cust Ret Last Week | XRank
-----------------------------------------------------------
ProdA | Z3 | 150 | 1
ProdA | Z1 | 100 | 2
ProdA | Z2 | 5 | 3
ProdB | Z1 | 400 | 1
ProdB | Z6 | 300 | 2
ProdB | Z2 | 75 | 3
ProdB | Z8 | 4 | 4

Thanks for any help!

When you say that nested Order() does not work, can you give a Foodmart
query example, since it seems OK here?

http://groups-beta.google.com/group/...rver.olap/msg/
11a6da7a23622e07?hl=en
From: Deepak Puri <deepak_p... (AT) progressive (DOT) com>
Date: Wed, 19 Jan 2005 15:18:42 -0800

Subject: Re: Order results descending by Member Properties

Hi Francisco,

Does this version give you the desired ordering?

SELECT
{
[Unit Sales]

} ON COLUMNS,

Order(Order(Order(NonEmptyCros*sJoin
([Store].[Store Name].Members,
[Product].[Product Family].Members),
[Store].Properties("Store Manager"), BDESC),
[Store].CurrentMember.Name, BDESC),
[Store].Parent.Name, BDESC)
DIMENSION PROPERTIES
[Store].[Store State].[Name],
[Store].[Store City].[Name],
[Store].[Store Name].[Name],
[Product].[Product Family].[Name],
[Store].[Store Name].[Store Manager],
[Store].[Store Name].[Store Sqft]
ON ROWS
FROM Sales

Newsgroups: microsoft.public.sqlserver.olap
From: "franciscoalvarado" <franciscoalvar... (AT) gmail (DOT) com>
Date: 19 Jan 2005 19:22:17 -0800

Subject: Re: Order results descending by Member Properties

This is perfect!
Exactly what I was looking for.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,

Working from your example I have solved my problem. I don't fully
understand the results of similar queries though. Here is the MDX that
solves the problem:

SELECT
{[Measures].[Cust Ret Last Week]} ON COLUMNS,
Order(
Order(
NonEmptyCrossJoin({[Product Family].[All Product
Family].children},
{topcount([Attributed
Class].[All Attributed Class].[WDF].children, 10, [Measures].[Cust Ret
Last Week])}),
[Measures].[Cust Ret Last Week], BDESC),
[Product Family].CurrentMember.Name, BASC) ON ROWS
FROM
[Onion Peel]
WHERE
([Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid])

It sorts by [Measures].[Cust Ret Last Week] then sorts by [Product
Family].CurrentMember.Name. In the end both sorts are obeyed.

However, if I don't use the "B" variants (which is what I was doing
yesterday):

SELECT
{[Measures].[Cust Ret Last Week]} ON COLUMNS,
Order(
Order(
NonEmptyCrossJoin({[Product Family].[All Product
Family].children},
{topcount([Attributed
Class].[All Attributed Class].[WDF].children, 10, [Measures].[Cust Ret
Last Week])}),
[Measures].[Cust Ret Last Week], DESC),
[Product Family].CurrentMember.Name, ASC) ON ROWS
FROM
[Onion Peel]
WHERE
([Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid])

the result is sorted ASC by [Product Family].CurrentMember.Name but not
DESC by [Measures].[Cust Ret Last Week]. The outer order() seems to
override the inner order().

If I change the order of the sorts and use the "B" variants:

SELECT
{[Measures].[Cust Ret Last Week]} ON COLUMNS,
Order(
Order(
NonEmptyCrossJoin({[Product Family].[All Product
Family].children},
{topcount([Attributed
Class].[All Attributed Class].[WDF].children, 10, [Measures].[Cust Ret
Last Week])}),
[Product Family].CurrentMember.Name, BASC),
[Measures].[Cust Ret Last Week], BDESC) ON ROWS
FROM
[Onion Peel]
WHERE
([Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid])

The result is sorted BDESC by [Measures].[Cust Ret Last Week] but not
BASC by [Product Family].CurrentMember.Name.

Does that make sense to you? Like I said, I'm new to MDX so I'm not
clear as to why these results end up the way they do. I'll try to
duplicate this in Foodmart over the weekend. Anyway, thanks for the
example as this helped me find my way. Now on to incorporating rank().

Cheers!

When you don't specify the "Break Hierarchy" in Order(), results can be
confusing - in this case, since the set tuples have members of 2
dimensions, you have to consider hierarchy of [Product Family], then
[Attributed Class].


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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