Question Status

Unanswered
Herman asked a question on 11 Apr 2013 2:38 AM

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!

Reply
Bilal Issa responded on 11 Apr 2013 5:57 AM

Hi ,

can you please try to use the order function.

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

Regards,

Bilal

Reply
Herman responded on 11 Apr 2013 6:38 AM

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?

Reply
Bilal Issa responded on 11 Apr 2013 7:09 AM

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

Reply
Dick Wenning responded on 11 Apr 2013 10:15 AM

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

Kind regards, 

Kaya Solutions

Dick Wenning

+31 6 147 989 53 

Landjuweel 5

3905 PE - Veenendaal

 

OTHER CONTACT INFORMATION

Reply