Stairway to MDX - STEP 3: The Order() Function

Question

Post reply

Stairway to MDX - STEP 3: The Order() Function

Bill Pearson

SSCommitted

Points: 1520
More actions
February 14, 2011 at 10:37 am

#388853

Comments posted to this topic are about the item Stairway to MDX - STEP 3: The Order() Function

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Roland Brown SSC Enthusiast Points: 149 More actions · Answer 1

Item 3 code mismatch:

{[Geography].[Geography].[State-Province].[Alabama].CHILDREN,

[Geography].[Geography].[State-Province].[Alabama].CHILDREN}

s/b Alabama, Mississippi

jeff122877 Mr or Mrs. 500 Points: 535 More actions · Answer 2

I'm going to leave this right here, even though I think it might just get answered in the next Stairway article.

In the example from STEP 3, how could I sort by Resellers Sales Amount DESC without the State_Province natural heirarchy breaking it up into what to me is two separate sorGed lists. I want to see Resellers Sales Amount in DESC order with out grouping by state.

Great article so far. It's giving me the kick start to MDX that I needed (I wanted to learn MDX but didn't know where to start).

EDIT: Just as I thought. BASC and BDESC will do this and are explained in the next Step

Jeff.

rgtft Hall of Fame Points: 3518 More actions · Answer 3

I get this error for the below code:

Query (11, 7) Parser: The syntax for 'ORDER' is incorrect.

-- SMDX003-004 Simple Use of Order() function across columns,

-- sort descending(respecting hierarchies)

SELECT

NONEMPTY

ORDER(

{[Geography].[Geography].[State-Province].[Alabama].CHILDREN,

[Geography].[Geography].[State-Province].[Mississippi].CHILDREN},

[Measures].[Reseller Sales Amount], DESC

) ON AXIS(0),

{[Measures].[Reseller Sales Amount]} ON AXIS(1)

FROM

[Adventure Works]

What's wrong with the syntax?

TIA,

Rob

sudeep.puvadi Valued Member Points: 61 More actions · Answer 4

hi all,

I just read the blog and still confused about the functionality of this order function.

According to the blog, when we add DESC keyword to the ORDER function, the data has to be sorted by fact value without any break in the natural hierarchy of dimensions, but why even the Alabama and Mississippi children are sorted ( I mean swapped).

I was expecting first the children of Alabama sorted by fact value DESC and then children of Mississippi sorted by fact value DESC.

Please help me in understanding this.

Thanks in Advance.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

Bill_Pearson - Monday, February 14, 2011 10:37 AM
Comments posted to this topic are about the item Stairway to MDX - STEP 3: The Order() Function

I suppose I'll get flamed for this idea!

Once again I'm wondering why MDX doesn't borrow as much as possible from ANSI SQL. In this case, the ORDER BY clause. Imagine you could do this:

SELECT

[Measures].[Reseller Sales Amount] OVER(AXIS 0) as rsa, [Geography].[Geography].[State-Province].[Alabama].CHILDREN OVER(AXIS 1) AS alabama, [Geography].[Geography].[State-Province].[Mississippi].CHILDREN OVER(AXIS 1) AS miss FROM AdventureWorks WHERE <something> IS NOT NULL ORDER BY alabama, miss, rsa DESC

No braces, no functions. Just SQL with some extra elements. That kind of thing would flatten the learning curve a bit, I think.

Alas! The ship has sailed.