Sorting on dimension not happening properly.

# Sorting on dimension not happening properly.

• Friday, June 15, 2012 1:19 PM

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

### All Replies

• Friday, June 15, 2012 1:39 PM

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

Suhas Kudekar

##### My Blog

• Friday, June 15, 2012 1:53 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

• Monday, July 02, 2012 7:07 AM

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 8:28 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

Philip,

• Marked As Answer by Tuesday, July 10, 2012 10:15 AM
•
• Monday, July 02, 2012 8:32 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