[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MDX Order By

Posted on 2013-01-23
7
Medium Priority
?
611 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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