MDX function ORDER over CROSSJOINed Set

Hello!

I have simple MDX query (SSAS2005):

SELECT
{ [Date].[Calendar].[Calendar Year].&[2004] } ON COLUMNS ,
ORDER(
DESCENDANTS( [Product].[Product Model Categories].[All Products], [Product].[Product Model Categories].[Subcategory] ) *
{[Product].[Size].[All Products], DESCENDANTS([Product].[Size].[All Products], [Product].[Size].[Size]) },
[Measures].[Reseller Sales Amount],
DESC) ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )

Why members of [Product].[Product Model Categories].[Subcategory] is not arranged?

It is, you haven't specified to break the hierarchy order in the ORDER function and you're not sorting by the column you're displaying. Try the following query:

SELECT

{ [Date].[Calendar].[Calendar Year].&[2004] } ON COLUMNS ,

ORDER(

DESCENDANTS( [Product].[Product Model Categories].[All Products], [Product].[Product Model Categories].[Subcategory] ) *

{[Product].[Size].[All Products], DESCENDANTS([Product].[Size].[All Products], [Product].[Size].[Size]) },

([Measures].[Reseller Sales Amount],[Date].[Calendar].[Calendar Year].&[2004]),

BDESC) ON ROWS

FROM [Adventure Works]

WHERE ( [Measures].[Reseller Sales Amount] )

I've highlighted in red the bits that I've added. It's easy to forget to add the 'B' in front of ASC and DESC, and you have to remember that ORDER won't pick up on any selection you've made on columns - you need to specify the column you want to sort by by adding it to the tuple in the second parameter.

HTH,

Chris

|||

Hi, Chris! Thanks for answer, but...

the hierarchy should be kept. Members of both dimensions should be arranged in the descending order. In the MSAS2000 function ORDER worked correctly. Here is a similar example for Foodmart2000, "Cities" are sorted in the desc and "Education level" is sorted inside of cities too.

SELECT { [Time].&[1997] } ON COLUMNS ,
ORDER(
{
DESCENDANTS( [Customers].[All Customers], [Customers].[City] ) *
{ [Education Level].[All Education Level], DESCENDANTS( [Education Level].[All Education Level], [Education Level].[Education Level] ) }
},
( [Time].&[1997] ),
DESC
) ON ROWS
FROM [Sales]
WHERE ( [Measures].[Unit Sales] )

http://img206.imageshack.us/img206/3055/ordermsas200eg4.jpg

|||

Ah, I see what you mean. This looks like a bug to me - you should report it at Connect (http://connect.microsoft.com/).

In the meantime, does the following query do what you want?

SELECT

{ [Date].[Calendar].[Calendar Year].&[2004] } ON COLUMNS ,

GENERATE(

ORDER(

DESCENDANTS( [Product].[Product Model Categories].[All Products], [Product].[Product Model Categories].[Subcategory] )

,([Measures].[Reseller Sales Amount],[Date].[Calendar].[Calendar Year].&[2004]), DESC)

, [Product].[Product Model Categories].CURRENTMEMBER

*

ORDER(

{[Product].[Size].[All Products], DESCENDANTS([Product].[Size].[All Products], [Product].[Size].[Size]) }

,([Measures].[Reseller Sales Amount],[Date].[Calendar].[Calendar Year].&[2004],[Product].[Product Model Categories].CURRENTMEMBER), DESC)

)

ON ROWS

FROM [Adventure Works]

WHERE ( [Measures].[Reseller Sales Amount] )

Chris

|||

I have made so, but If I need 5-7 crossjoined dimensions?
So many calls instead one ORDER!!!

Thanks.