Results 1 to 2 of 2

Thread: How hard can it be to sort MDX descending - help please

  1. #1
    Join Date
    Nov 2017
    Posts
    2

    Question How hard can it be to sort MDX descending - help please

    Hello,
    In SQL I just put "Order By {whateverfield}" at the end my statement, but in MDX I just can't work it out.

    I know about "ORDER" and the DESC or BDESC, but can't fit it into the following without breaking it or it having no effect:
    Code:
    WITH 
        MEMBER [Measures].[ParameterCaption] 
        AS[Financial Date].[Short Year-Month (H)].CURRENTMEMBER.MEMBER_CAPTION 
        MEMBER [Measures].[ParameterValue] 
        AS [Financial Date].[Short Year-Month (H)].CURRENTMEMBER.UNIQUENAME 
        MEMBER [Measures].[ParameterLevel] 
        AS [Financial Date].[Short Year-Month (H)].CURRENTMEMBER.LEVEL.ORDINAL 
    SELECT   
        {
    	  ORDER( [Measures].[ParameterCaption], DESC), 
    	  [Measures].[ParameterValue], 
    	  [Measures].[ParameterLevel]
        } 
        ON COLUMNS 
    ,
        [Financial Date].[Short Year-Month (H)].CHILDREN 
        ON ROWS 
    FROM 
        (   SELECT 
    		  ( { [Head Count].[Employee Status Code (H)].[Employee Status Code].&[A] } ) 
    		  ON COLUMNS 
    	   FROM 
    		  [Fact Head Count]
        )
    All I want to do is sort the following results on the ParameterCaption column descending.
    Mezer_11-28_16-21-44.png

    Many thanks in advance...

  2. #2
    Join Date
    Nov 2017
    Posts
    2

    Cool Add the ORDER on the ROWS and don't forget the measure

    Turns out to be easy as I found out myself after a night's sleep:
    Code:
    WITH 
        MEMBER [Measures].[ParameterCaption] 
        AS[Financial Date].[Short Year-Month (H)].CURRENTMEMBER.MEMBER_CAPTION 
        MEMBER [Measures].[ParameterValue] 
        AS [Financial Date].[Short Year-Month (H)].CURRENTMEMBER.UNIQUENAME 
        MEMBER [Measures].[ParameterLevel] 
        AS [Financial Date].[Short Year-Month (H)].CURRENTMEMBER.LEVEL.ORDINAL 
    SELECT   
        {
    	  [Measures].[ParameterCaption], 
    	  [Measures].[ParameterValue], 
    	  [Measures].[ParameterLevel]
        } 
        ON COLUMNS 
    ,
        Order(	  [Financial Date].[Short Year-Month (H)].CHILDREN, 
    	  	  [Measures].[ParameterValue]  , 
    	  	  DESC
    	     )
        ON ROWS 
    FROM 
        (   SELECT 
    		  ( { [Head Count].[Employee Status Code (H)].[Employee Status Code].&[A] } ) 
    		  ON COLUMNS 
    	   FROM 
    		  [Fact Head Count]
        )
    Result:
    Mezer_11-29_09-41-31.png

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •