MDX: Sorting on an attribute

[20 Ton Squirrel]

I have a dimension called DSD Month that lists out every month from 1996 to 2030. The element names go like "Mar 1996, Apr 1996, May 1996, …"

There's also an attribute called 'Year-Month' that goes like "1996-03, 1996-04, 1996-05, …"

For a particular subset I just want the end month of each quarter (Mar, Jun, Sep, Dec). I also need it sorted chronologically, of course.

My MDX is as follows:

Code: Select all

ORDER (
      
        UNION ( 
                  UNION (  TM1FILTERBYPATTERN ( TM1FILTERBYLEVEL ( TM1SUBSETALL ( [ DSD Month ] ) , 0 ) , "Mar *" ) 
                         , TM1FILTERBYPATTERN ( TM1FILTERBYLEVEL ( TM1SUBSETALL ( [ DSD Month ] ) , 0 ) , "Jun *" ) )
                , UNION (  TM1FILTERBYPATTERN ( TM1FILTERBYLEVEL ( TM1SUBSETALL ( [ DSD Month ] ) , 0 ) , "Sep *" )
                         , TM1FILTERBYPATTERN ( TM1FILTERBYLEVEL ( TM1SUBSETALL ( [ DSD Month ] ) , 0 ) , "Dec *" ) )
              )
       , [ DSD Month ].[Year-Month]
       , ASC
      )

The MDX works... sorta. I get the proper elements filtered but the sorting is wonky for 1996-1997, everything AFTER 1997 sorts fine.

It goes as follows:

• Mar 1996
• Mar 1997
• Jun 1996
• Jun 1997
• Sep 1996
• Sep 1997
• Dec 1996
• Dec 1997
• Mar 1998
• Jun 1998
• Sep 1998
• Dec 1998
• …

Any ideas on why this would occur? Any thoughts on better means of approaching would be appreciated, also

[gtonkin]

Try change ASC to BASC

Order MDX
"...If BASC or BDESC is specified, the Order function arranges members in the set without regard to the hierarchy...."

[20 Ton Squirrel]

You, good sir, are a gentleman and a genius. An ingenious gentleman.

I should've looked up that function, such a simple solution! It works perfectly now, thanks very much. n__n