locked
Order by on Dimension attributes RRS feed

  • Question

  • Hello,

    In one of my report, i am just using the attributes from dimensions to show the report.But client wants the data to be ordered in descending by one of the attribute. How can we achiev this.

    I am using the following query.

    SELECT

    {}

    ON

    COLUMNS,

    { [Task].[Project Number].[Project Number].

    ALLMEMBERS } *

    { [Task].[Name].[Name].

    ALLMEMBERS } *

    { [Task].[Planned Finish Date].[Planned Finish Date].

    ALLMEMBERS } *

    { [Task].[Actual Finish Date].[Actual Finish Date].

    ALLMEMBERS } *

    { [Task].[Forecast Finish Date].[Forecast Finish Date].

    ALLMEMBERS } *

    { [Task].[Weeks Delta To Current Plan].[Weeks Delta To Current Plan].

    ALLMEMBERS }
     

    ON

    ROWS

    FROM

    [Cube]

    Where

    [Task].[Revenue Package Number].&[112233]

    Records should be ordered by Planned finish date. Please help me on this.

    Thanks

    Tuesday, October 11, 2011 9:19 PM

Answers

  • Enclose ROWS in an ORDER function as:

    ORDER ( 
    { [Task].[Project Number].[Project Number].
    
    ALLMEMBERS } *
    { [Task].[Name].[Name].
    
    ALLMEMBERS } *
    { [Task].[Planned Finish Date].[Planned Finish Date].
    
    ALLMEMBERS } *
    { [Task].[Actual Finish Date].[Actual Finish Date].
    
    ALLMEMBERS } *
    { [Task].[Forecast Finish Date].[Forecast Finish Date].
    
    ALLMEMBERS } *
    { [Task].[Weeks Delta To Current Plan].[Weeks Delta To Current Plan].
    
    ALLMEMBERS } 
    , [Task].[Planned Finish Date].[Planned Finish Date].CurrentMember
    , DESC
    )


    The last line will determine the order of sort. Choose between DESC and BDESC to get the result you want. More information here:

    ORDER (MDX)


    http://dailyitsolutions.blogspot.com/
    • Marked as answer by Mike RGO Wednesday, October 12, 2011 5:32 PM
    Wednesday, October 12, 2011 3:34 PM

All replies

  • Enclose ROWS in an ORDER function as:

    ORDER ( 
    { [Task].[Project Number].[Project Number].
    
    ALLMEMBERS } *
    { [Task].[Name].[Name].
    
    ALLMEMBERS } *
    { [Task].[Planned Finish Date].[Planned Finish Date].
    
    ALLMEMBERS } *
    { [Task].[Actual Finish Date].[Actual Finish Date].
    
    ALLMEMBERS } *
    { [Task].[Forecast Finish Date].[Forecast Finish Date].
    
    ALLMEMBERS } *
    { [Task].[Weeks Delta To Current Plan].[Weeks Delta To Current Plan].
    
    ALLMEMBERS } 
    , [Task].[Planned Finish Date].[Planned Finish Date].CurrentMember
    , DESC
    )


    The last line will determine the order of sort. Choose between DESC and BDESC to get the result you want. More information here:

    ORDER (MDX)


    http://dailyitsolutions.blogspot.com/
    • Marked as answer by Mike RGO Wednesday, October 12, 2011 5:32 PM
    Wednesday, October 12, 2011 3:34 PM
  • Thanks...got it
    Wednesday, October 12, 2011 5:32 PM