MDX sort

This question is not answered

I'm trying to sort this query on the week number [Calendar ISO Week] but can't make it work.

Can someone help us out please, we're all learning MDX in our company.

SELECT NON EMPTY { [Measures].[Sales Invoice Transactions Line Amount] } ON COLUMNS, NON EMPTY { ([Sales Invoice Invoice Date].[Calendar Year Number].[Calendar Year Number].ALLMEMBERS * [Sales Invoice Invoice Date].[Calendar ISO Week].[Calendar ISO Week].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@SalesInvoiceTransactionSalesGroupSalesGroupName, CONSTRAINED) ) ON COLUMNS FROM [Sales]) WHERE ( IIF( STRTOSET(@SalesInvoiceTransactionSalesGroupSalesGroupName, CONSTRAINED).Count = 1, STRTOSET(@SalesInvoiceTransactionSalesGroupSalesGroupName, CONSTRAINED), [Sales Invoice Transaction Sales Group].[Sales Group Name].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks!

All Replies
  • Hi ,

    can you please try to use the order function.

    msdn.microsoft.com/.../ms145587.aspx

    Regards,

    Bilal

  • Thanks you for your answer. We tried this yesterday and now again, but can't get it to work. Can you please help us with the syntax?

  • can you please try

    SELECT NON EMPTY { [Measures].[Sales Invoice Transactions Line Amount] } ON COLUMNS, NON EMPTY { ([Sales Invoice Invoice Date].[Calendar Year Number].[Calendar Year Number].ALLMEMBERS * order( [Sales Invoice Invoice Date].[Calendar ISO Week].[Calendar ISO Week].ALLMEMBERS,DESC) ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@SalesInvoiceTransactionSalesGroupSalesGroupName, CONSTRAINED) ) ON COLUMNS FROM [Sales]) WHERE ( IIF( STRTOSET(@SalesInvoiceTransactionSalesGroupSalesGroupName, CONSTRAINED).Count = 1, STRTOSET(@SalesInvoiceTransactionSalesGroupSalesGroupName, CONSTRAINED), [Sales Invoice Transaction Sales Group].[Sales Group Name].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  • open visual studio business intelligence, create a new report, (not the ax report)

    start the datasource wizard and start working from there, on the end copy the statement.

    trust me know one understands this logic.....