Hi All,

I'm having endless trouble displaying a resultset that is ordered as
Level 1 ASC, Level 2 DESC... The data I am trying to sort for e.g.
looks as follows...

[Province] [Category] [Measures].[Sales]
eastern cape A 125
eastern cape B 184
eastern cape C 1092
eastern cape D 12
gauteng A 144
gauteng B 1234
gauteng C 567
gauteng D 5
mpumalanga A 2345
mpumalanga B 1234
mpumalanga C 5676
mpumalanga D 665

My order clause should perform in some what the same way as shown in
the SQL statement below:

.... SELECT STATEMENT

GROUP BY
[PROVINCE]
ORDER BY
[Province] ASC, [Sales] DESC

And the desired results should look as follows for e.g.:

[Province] [Category] [Measures].[Sales]
eastern cape C 1092
eastern cape B 184
eastern cape A 125
eastern cape D 12
gauteng B 1234
gauteng C 567
gauteng A 144
gauteng D 5
mpumalanga C 5676
mpumalanga A 2345
mpumalanga B 1234
mpumalanga D 665

I have tried various combinations of the ORDER clause in MDX to return
these desired results, from nested ORDER's to playing with BASC, BDESC
and ASC, DESC respectively, but still can't produce the desired result.
I cannot manage to group on [Province] in ASC order properly, and when
I do, it breaks the order of [Measures].[Sales] which should be DESC.

A sample of how the MDX query looks like that returns an unordered
resultset looks like this for e.g.:

SELECT
{[Measures].[Sales]} ON COLUMNS,

{NONEMPTYCROSSJOIN(
[Province],[Category])} ON ROWS
FROM
[CubeName]
WHERE
[Filter Dimension]

With each of the 3 columns being a complete seperate dimension,
[Province], [Category] & [Measures]

Any suggestions or help are greatly appreciated.

Many thanks,

Ryno Smit

One way of doing a nested ordering is to use the Generate function and
sort each set of categories separately.

To get the following code to work you will need to replace the
"<level>" text in the following code with the appropriate level names in
your Province and Category dimensions.

Generate([Province].[<level>].Members,
ORDER(NONEMPTYCROSSJOIN([Province].CurrentMember
,[Category].[<level>].Members),[Measures].[Sales],BDESC))
ON ROWS,
[Measures].[Sales] ON COLUMNS
FROM [Sales Summary]
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132572855.722529.230160 (AT) g44g2000cwa (DOT) googlegroups.com>,
ryno.smit (AT) gmail (DOT) com says...

Thanks Darren,

The Generate Function works 100%, I have solved my ordering issues...