web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / MDX sort
Microsoft Dynamics AX (Archived)

MDX sort

(0) ShareShare
ReportReport
Posted on by Herman 135

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!

*This post is locked for comments

I have the same question (0)
  • Bilal Issa Profile Picture
    Bilal Issa 4,370 on at

    Hi ,

    can you please try to use the order function.

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

    Regards,

    Bilal

  • Herman Profile Picture
    Herman 135 on at

    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?

  • Bilal Issa Profile Picture
    Bilal Issa 4,370 on at

    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

  • Dick Wenning Profile Picture
    Dick Wenning 8,705 Moderator on at

    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.....

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans