sqlsql-servervisual-studiomdxmdx-query

Order by meansure in MDX

I have problem in this query:

WITH MEMBER [Measures].[przychod] AS [Measures].[App Price]*[Measures].[Amount] SELECT NON EMPTY { [Measures].[przychod] } ON COLUMNS, NON EMPTY { ([Client].[City].[City].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DateTimeDateYear, CONSTRAINED) ) ON COLUMNS FROM [Data Warehouse]) WHERE ( IIF( STRTOSET(@DateTimeDateYear, CONSTRAINED).Count = 1, STRTOSET(@DateTimeDateYear, CONSTRAINED), [Date Time].[Date Year].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I would like to add order by [Measures].[przychod]. I tried all combination this query.

Solution

  • How about using the Order function:

    WITH MEMBER [Measures].[przychod] AS [Measures].[App Price]*[Measures].[Amount] 

    SELECT 

NON EMPTY { [Measures].[przychod] } ON COLUMNS, 
NON EMPTY Order( { ([Client].[City].[City].ALLMEMBERS ), [Measures].[przychod] } ),  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 

FROM ( SELECT ( STRTOSET(@DateTimeDateYear, CONSTRAINED) ) ON COLUMNS FROM [Data Warehouse]) WHERE ( IIF( STRTOSET(@DateTimeDateYear, CONSTRAINED).Count = 1, STRTOSET(@DateTimeDateYear, CONSTRAINED), [Date Time].[Date Year].currentmember ) ) 

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS