Monday, March 19, 2012

MDX's ORDER Function

Hi

I'm trying to order rows returned by a MDX query using ORDER function. It seems that I don't use it properly. Here is the query from the sample AS Database, AdventureWork.

WITH MEMBER [Measures].DIFF AS
'([Customer].[Country].CURRENTMEMBER, [Measures].[Internet Sales Amount])
-
([Customer].[Country].&[Australia], [Measures].[Internet Sales Amount])'
SELECT [Measures].DIFF ON COLUMNS,
ORDER([Customer].[Country].MEMBERS, [Measures].DIFF, BDESC) ON ROWS
FROM [Adventure Works]

what it returns is:

All Customers $20,297,676.64
United States $328,788.93
Australia $0.00
United Kingdom ($5,669,288.37)
Germany ($6,166,688.25)
France ($6,416,982.87)
Canada ($7,083,155.72)

I expected to see "Australia" at the end of the list and "All Customers" on top of it.
Any Idea?

Thanks in advance,
AmirThe results look correct to me. Why do you expect Australia at the end ? It's DIFF is 0 which is bigger then negative diffs of other countries.|||Sorry about that.
I didn't realize there is a bracket arround negative numbers.

Thanks

No comments:

Post a Comment