December 17, 2015 at 2:08 am
Hi all,
Quite confuse with reference site talking about sorting a query in MDX.
I have this query (build by Query designer in report builder) :
SELECT NON EMPTY { [Measures].[MultiLine Disc], [Measures].[RCP Disc], [Measures].[Profit], [Measures].[Net Amount], [Measures].[Margin], [Measures].[Gross Sales] } ON COLUMNS,
NON EMPTY { ([DateInvoice].[Month].[Month].ALLMEMBERS * [Project].[PROJID].[PROJID].ALLMEMBERS * [Project].[Project Name].[Project Name].ALLMEMBERS * [Dealer].[NAME].[NAME].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( { [DateInvoice].[Month].&[2015-11-01T00:00:00] } ) ON COLUMNS FROM ( SELECT ( { [Cost Centers].[Cost Center].&[COM], [Cost Centers].[Cost Center].&[RES] } ) ON COLUMNS FROM ( SELECT ( { [Company].[ID].&[8710] } ) ON COLUMNS FROM [HDSalesCube]))) WHERE ( [Company].[ID].&[8710], [Cost Centers].[Cost Center].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
the result column is like below :
ID Cost center Month SalesStatusDesc ProjectName ProjId Name MultiLineDisc RCPDisc GrossSales NetAmount Margin Profit
Question is how to add sorting from that MDX query ? I need to sort by ProjId then NetAmount
Please help. a desperate one.
Thanks,
axl
December 17, 2015 at 2:45 pm
You can use the ORDER clause directly in MDX:
https://www.mssqltips.com/sqlservertip/3129/order-and-sort-with-mdx-in-sql-server-analysis-services/
Or, since you're using SSRS, if you're not comfortable with writing it in the query you can just sort the Tablix that you intend to use the query result in.
December 17, 2015 at 11:12 pm
I tried to create a simple MDX myself -->
select { [Measures].[Gross Sales], [Measures].[Net Amount] } on 0,
NON EMPTY {
order ([Project].[PROJID].allmembers , [Measures].[Net Amount], desc)
* [Dealer].[NAME].allmembers
}
on 1
from [HDSalesCube]
Still it takes no effect to sort based on ProjID and Net Amount
Result -->
ProdID | Name | GrossSales | NetAmount
------ | ------ | ---------- | -----------
(null) | (null) | 115607842 | 111377088.54
(null) | A & S.. | 22173.0284 | 22173.0284
(null) | A.T.I... | 1972.2435 | 1972.2435
(null) | ACCE.. | 92906.2611 | 92906.2611
(null) | ACOT.. | 647202.425 | 647202.425
It looks, because I added Name, it will sort the Name as well. What I expected is, Line.5 should be no.2 because Net Amount is 647202.425, bigger than 22173.0284 (currently, line no.2)
Btw, at first, I did sort it, in SQL report builder. From Tablix property - Sorting, add sort key, 1st is ProjID (A to Z) then 2nd is NetAmount (Z to A), but it has no effect.
Appreciate any help.
Thanks.
December 17, 2015 at 11:58 pm
Following is the problem. you are ordering the project after that you are cross joining the Dealer dimension.
NON EMPTY {
order ([Project].[PROJID].allmembers , [Measures].[Net Amount], desc)
* [Dealer].[NAME].allmembers
}
on 1
if you want to order on the combination of the Project and Dealer you should be doing the following
NON EMPTY {
order ([Project].[PROJID].allmembers * [Dealer].[NAME].allmembers, [Measures].[Net Amount], desc)
}
on 1
hope it helps.
December 18, 2015 at 12:46 am
Hi,
You just solved my problem. TQVVVVVM 🙂
Although the performance is drop dramatically, because after that I added 2 more columns.
Btw just wondering, why in Report builder (or V.Studio) cannot handle this sorting ?
In the Tablix property (before I go to this MDX thing), I've set the sort key as I mentioned in my previous reply.
Anyway thank you, thank you very much.
GBU.
December 18, 2015 at 3:56 am
If you get the time check this Stairway to MDX[/url]. This series for MDX starts right at the very beginning and takes us through all the basic functions of MDX, with plenty of practical examples.
For better performance check the following link[/url]. Its quite comprehensive but have to read 🙂
This will help you to improve your MDX query performance, better query writting and other SSAS related performance.
hope it helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply