[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

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]
0
vbnetcoder
Asked:
vbnetcoder
  • 4
  • 3
1 Solution
 
Ryan McCauleyData and Analytics ManagerCommented:
Can you just wrap the ORDER BY column in a CDATE?

CDATE([Measures].[ParameterCaption])

Open in new window

0
 
vbnetcoderAuthor Commented:
I'm not sure where/how (very new to MDX) .... would you mind updating the code I posted with what you mean?
0
 
Ryan McCauleyData and Analytics ManagerCommented:
You should be able to do this:

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 

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
vbnetcoderAuthor Commented:
ryanmccauley,

When I run that code I get an error:

Parser: The end of the input was reached.
0
 
Ryan McCauleyData and Analytics ManagerCommented:
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

0
 
vbnetcoderAuthor Commented:
That worked ... thanks!!
0
 
vbnetcoderAuthor Commented:
TY
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now