Sorting MDX | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Sorting MDX

I am aware there is an ORder function to sorting data using three parameters: the set of members to oredr, value to sort by, and sort order…. is there any function for me to sort the date, would this work?? SELECT {[Measures].Members } ON COLUMNS,
{ Order ({[Accounting Date]. Members } ,( [Accounting Date].[Account Year]) , BASC ) } ON ROWS FROM
[Accounts Sales] please help…im quite new to MDX….. so bit clueless!
Maybe you should better clarify how your dimension is built and how you would like to sort…
sorry for being so unclear…. I have a query that displays measurses such as VAT on the column against dates, the date dimension is made up of Year, Quarter Month, Week No.
The year and quarter are fine but some months do not appear in the correct order eg feb comes befor jan, and so the weeks are not in correct order either. How do I correct this??
Has the Month attribute both a key and a name, different from key? Have you set the Order property of the attribute on the key?
sorry im a bit new to this and dont no too much about keys…. the order by has been set to Name, and Month is a member name same as Year, Quarter, Week No and day I have set it wrong/ shoul dit be sorted by keys???
If you have, for instance, for the month attribute:
Key | Name
01 | January
02 | February
03 |March

Of course you should order by key
i think I understand what ur sayin, so if I had the data sorted by key ( like in SQL tables u often have a primary key)… then I could sort the data by the key, correct??? Problem is, the Date dimension has Year, Quarter, Month, Week No, Day… the user has 4 options, they can either see teh data on yearly basis, or monthly basis, or month basic, or week basic… how can I correct a order function to order the data depending on what the user chooses??? each table would have a different primary key rite…. When writing the Order function, I have three parameters: what I want to sort, order on what, and on what order… so would that be the date dimension, maybe month, in asc key order??? eg SELECT {[Measures].Member} ON COLUMNS,
{Order ([Date].Members, [Date].[Month], ASC ) } ON ROWS
….. would something like this work….. currently (without any sort) all of the data is in correct order, apart some months, and hence the weeks are wrong…. HOPE U CAN HELP
(a thread had been started with the samme problem by someone else but they dont explain if they got it worked out!!!) THANKS

If you have several levels/attributes for a dimension (like month, week, and so on), you can set the order property in the "Advanced properties" for each level. Further, you have a member key and a member name for each level.
I suggest you to buy a good book teaching using Analysis Services!
yeah i plan to buy 1…ta!! u got any suggestions for any good books,
thanks!!!
Microsoft Offical Course book, named "Designing and Implementing OLAP Solutions with Microsoft SQL Server 2000" is very good. Don’t know if it can be bought without participating to the course. You should try to discover it.

The Analysis Services 2000 Ops guide and documentation on MSDN is also helpful. Raulie
@hp All postings are provided “AS IS” with no warranties for accuracy.

Hi ALL, Please do share if you are aware of a place from where the book suggested by Vania is available.
Hi Raulie, Can you kindly share the URL from where I can go through the
‘The Analysis Services 2000 Ops guide’
Thanks.

]]>