MS SQL Server OLAP - MDX Multiple ORDER Help

Author

MDX Multiple ORDER Help

ryno.smit@gmail.com

2005-11-21, 7:23 am

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,

& #123;NONEMPTYCROSSJO
IN(
[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

Darren Gosbell

2005-11-21, 8:24 pm

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.

SELECT
Generate([Province].[<level>].Members,
ORDER(NONEMPTYCROSSJ
OIN([Province].CurrentMember
,[Category].[<level>].Members),[Measures].[Sales],BDESC))
ON ROWS,
[Measures].[Sales] ON COLUMNS
FROM [Sales Summary][color=darkr
ed]

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132572855.722529.230160@g44g2000cwa.googlegroups.com>,
ryno.smit@gmail.com says...[color=darkred]
> 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

ryno.smit@gmail.com

2005-11-28, 7:24 am

Thanks Darren,

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

Sponsored Links