asked on

MDX Order By

In the following MDX i need to order by [Measures].[ParameterCaption].....
It IS ordering now but it is doing it by alphabetically ..... i need it to treat [Measures].[ParameterCaption] as a date and order it that way

WITH MEMBER [Measures].[ParameterCaption] AS [Accounting Period].[Accounting Period Name].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Accounting Period].[Accounting Period Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Accounting Period].[Accounting Period Name].CURRENTMEMBER.LEVEL.ORDINAL

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] } ON COLUMNS,
ORDER( {[Accounting Period].[Accounting Period Name].ALLMEMBERS},[Measures].[ParameterCaption], desc) ON ROWS

FROM [MasterCube]

Ryan McCauley

Can you just wrap the ORDER BY column in a CDATE?

CDATE([Measures].[ParameterCaption])

Open in new window

vbnetcoder

ASKER

I'm not sure where/how (very new to MDX) .... would you mind updating the code I posted with what you mean?

ASKER CERTIFIED SOLUTION

Ryan McCauley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnetcoder

ASKER

ryanmccauley,

When I run that code I get an error:

Parser: The end of the input was reached.

Ryan McCauley

I'm sorry - I missed the FROM statement at the end of your original MDX. Just add it back to the query and it should work:

WITH MEMBER [Measures].[ParameterCaption] AS
             [Accounting Period].[Accounting Period Name].CURRENTMEMBER.MEMBER_CAPTION 
     MEMBER [Measures].[ParameterValue] AS 
             [Accounting Period].[Accounting Period Name].CURRENTMEMBER.UNIQUENAME 
     MEMBER [Measures].[ParameterLevel] AS 
             [Accounting Period].[Accounting Period Name].CURRENTMEMBER.LEVEL.ORDINAL 

SELECT {[Measures].[ParameterCaption],
        [Measures].[ParameterValue],
        [Measures].[ParameterLevel] } ON COLUMNS, 
ORDER( {[Accounting Period].[Accounting Period Name].ALLMEMBERS},
         CDATE([Measures].[ParameterCaption]), desc) ON ROWS 

FROM [MasterCube]

Open in new window

vbnetcoder

ASKER

That worked ... thanks!!

vbnetcoder

ASKER