[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MDX Order By

Posted on 2013-01-23
7
Medium Priority
?
606 Views
Last Modified: 2013-01-28
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
Comment
Question by:vbnetcoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38813086
Can you just wrap the ORDER BY column in a CDATE?

CDATE([Measures].[ParameterCaption])

Open in new window

0
 

Author Comment

by:vbnetcoder
ID: 38813983
I'm not sure where/how (very new to MDX) .... would you mind updating the code I posted with what you mean?
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 2000 total points
ID: 38815002
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:vbnetcoder
ID: 38818493
ryanmccauley,

When I run that code I get an error:

Parser: The end of the input was reached.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38818964
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
 

Author Comment

by:vbnetcoder
ID: 38827301
That worked ... thanks!!
0
 

Author Closing Comment

by:vbnetcoder
ID: 38827306
TY
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question