View Full Version : Sort a Level by a Measure
mondrian_huyhoang1970
08-09-2005, 04:48 PM
How can I sort a level by a Measures and the sorting doesn't break the hierarchy? Thanks
jhyde
08-09-2005, 08:39 PM
ORDER(<Set>, <Numeric expression>, DESC)
BDESC (or BASC) breaks the hierarchy; DESC (or ASC) does not.
Example:
SELECT {[Measures].[Unit Sales]} ON COLUMNS,
ORDER({[Product].MEMBERS} ,
[Measures].Unit Sales],
DESC) ON ROWS
FROM [Sales]
mondrian_huyhoang1970
08-10-2005, 03:29 PM
Thanks for your promt answer. I used this solution and it worked fine but the order lost whenever I do a drill down or up (by clicking + or - buttons in JPivot). Could you please explain this. Thanks
jhyde
08-11-2005, 08:16 AM
I'm not an expert on JPivot, but it might be a JPivot bug.
mondrian_huyhoang1970
08-22-2005, 08:35 PM
Hi Julian,
I asked the JPivot guys regarding to this problem and received the following answer:
By: Andreas Voss - avix
RE: Ordering Lost when doing drill down or up
2005-08-12 06:23
This is more a Mondrian related problem. The reason is that some sorting is done in the java code and other is done by the database. If you can switch your database to sort the same way as java does (e.g. case sensitive), it should be no difference.
Could you please show me how to resolve the problem. Thanks alot.