MDX query order issue

0
votes

I have two dimension field say [Custom Date] which have data like 'September 2009' and [Custom Date With Formart] which have data like 'Sep-09'.

I am applying order function to arrange it as shown below.

Order(BottomCount(
{[CUBE].[Custom Date].[Custom Date].ALLMEMBERS},  12),
[CUBE].[Custom Date].[Custom Date].[], DESC)
ON COLUMNS,

It arranges my dates as 'September 2009','October 2009'....,'January 2010','February 2010'.

Order(
{[CUBE].[Custom Date With Formart].[Custom Date With Formart].ALLMEMBERS},
[CUBE].[Custom Date].[Custom Date].[], DESC)
ON COLUMNS,

Output for above is like 'Jan-10','Feb-10',....,'Sep-09','Oct-09'.

What i want is to display [Custom Date With Formart] on axis and sort it using [Custom Date].

Let me know your comments.

Thanks to all of you. I resolved my issue by following this link.

2
sql-server-2005mdx
Any solutions please.!!! – ashish.chotalia
What's the current output? What's your expected output? – DHN
Current out put is like Output for above is like 'Jan-10','Feb-10',....,'Sep-09','Oct-09'. But it's not in ascending order. It should be 'Sep-09,'Oct09'....,'Jan-10','Feb-10' – ashish.chotalia

2 Answers

2
votes

I'm not sure to really understand your issue. Why do you need any specific ordering to get what seems to be the natural ordering of a time dimension. In any case, it might be that you need to use the BDESC option instead of DESC (see documentation here).

1
votes

You could try creating a string to sort on, and converting it to a date, if you can cope with VBA:

ORDER(whatever, CDate("01-" + [my dimension].currentMember.caption), BDESC)

That code may not run as it is, but what I'm trying to suggest is that you take the string of your member name "Sep 10" and turn it into a string with a full date in (first of the month), which can then be converted into a date, and used to sort on.