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

  • Bill Pearson

    Ten Centuries

    Points: 1349

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

  • Roland Brown

    SSC Enthusiast

    Points: 149

    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: 525

    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: 3517

    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

    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: 6520

    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.

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

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