none
Sorting on dimension not happening properly.

    Question

  • the below code is not providing correct order for DESC but coming correct in ASC. Any solutions?

    WITH
    SET S AS
    ORDER(
      [Delivery Date].[Calendar].[Month].MEMBERS
      ,[Delivery Date].[Month of Year].CURRENTMEMBER.PROPERTIES("KEY0")
      ,DESC
    )
    MEMBER [KEY0] AS [Delivery Date].[Month of Year].CURRENTMEMBER.PROPERTIES("KEY0")

    SELECT {[KEY0]} ON 0
           ,{S} ON 1
    FROM [Adventure Works]

    Friday, June 15, 2012 1:19 PM

Answers

  • Try the following:

    WITH
     SET S AS
     ORDER(
       [Delivery Date].[Calendar].[Month].MEMBERS
       ,rank([Delivery Date].[Calendar].CURRENTMEMBER,[Delivery Date].[Calendar].[Month])
       ,BDESC
     )
    
     MEMBER [MyRank] AS rank([Delivery Date].[Calendar].CURRENTMEMBER,[Delivery Date].[Calendar].[Month])
     
    SELECT {[MyRank]} ON 0
            ,{S} ON 1
     FROM [Adventure Works]

    Philip,

    • Marked as answer by ArnayJoshi Tuesday, July 10, 2012 10:15 AM
    Monday, July 02, 2012 8:28 AM

All replies

  • Hi Arnay,

    I think the Problem is with "[Delivery Date].[Month of Year]". Use belw MDX hope it solved your problem Or let me know if you get any other approch.

    WITH
     SET S AS
     ORDER(
       [Delivery Date].[Calendar].[Month].MEMBERS
       ,[Delivery Date].[Calendar].CURRENTMEMBER.PROPERTIES("KEY0")
       ,DESC
     )
    
     MEMBER [KEY0] AS [Delivery Date].[Calendar].CURRENTMEMBER.PROPERTIES("KEY0")
     
    SELECT {[KEY0]} ON 0
            ,{S} ON 1
     FROM [Adventure Works]

    Suhas Kudekar


    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    My Blog
    Follow @SuhasKudekar

    Friday, June 15, 2012 1:39 PM
  • Hi Arnay

    What is it that you want to achieve with your statement? With Suhas's statement you will get the data sorted according to monthly key of the hierarchy. I assume you have a month (by year - Jan 2012 to Dec 2099) and then the month of year (simply the month - Jan to Dec).

    So you are either trying to display the months in terms of the years

    Jan 2012, Feb 2012 .... Nov 2099, Dec 2099

    , or you want the month, by year:

    Jan 2012, Jan 2013 ..... Dec 2098, Dec 2099

    Also how are the attributes linked to each other, as your query may be possible by using the Month of Year Property, rather than the Key of the attribute, or even writing the query in a different way - Month of Year * Calendar.Year

    Friday, June 15, 2012 1:53 PM
  • It should sort by year and month in desc order which means ... 2012 09, 2012 08, ... ,2010 12, 2012 11, ... 2008 01 etc ... and the above query only sorts by year and not month.

    Monday, July 02, 2012 7:07 AM
  • Try the following:

    WITH
     SET S AS
     ORDER(
       [Delivery Date].[Calendar].[Month].MEMBERS
       ,rank([Delivery Date].[Calendar].CURRENTMEMBER,[Delivery Date].[Calendar].[Month])
       ,BDESC
     )
    
     MEMBER [MyRank] AS rank([Delivery Date].[Calendar].CURRENTMEMBER,[Delivery Date].[Calendar].[Month])
     
    SELECT {[MyRank]} ON 0
            ,{S} ON 1
     FROM [Adventure Works]

    Philip,

    • Marked as answer by ArnayJoshi Tuesday, July 10, 2012 10:15 AM
    Monday, July 02, 2012 8:28 AM
  • Ah, the problem with the month attribute is that it is comprised of a composite key - "Year"  and "Month of Year" so when you reference Key0 you only get the year attribute, and then the ordering beyond year, will be based on the actual dimension ordering.

    You can try and play around with the member keys and generate the correct property, or you can simply order by the UniqueName:

    WITH
     SET S AS
     ORDER(
       [Delivery Date].[Calendar].[Month].MEMBERS
       ,[Delivery Date].[Calendar].CURRENTMEMBER.UNIQUENAME
       ,DESC
     )
     MEMBER [KEY0] AS [Delivery Date].[Calendar].CURRENTMEMBER.PROPERTIES("KEY0")
     
    SELECT {[KEY0]} ON 0
            ,{S} ON 1
     FROM [Adventure Works]

    Monday, July 02, 2012 8:32 AM
  • Thanks VHteghem_Ph [sorry if this is not how you like to be addresses :) ] for the help.

    I was needing the keys as month number and the query works fine.

    Tuesday, July 10, 2012 10:16 AM