Hi!

We've been struggling a lot with this one Would appreciate any pointers..

The basic query is like this:
SELECT
[Measures].[Amount] ON COLUMNS,
NON EMPTY CROSSJOIN([Time].[2003].Children, [ProductGroup].[All
ProductGroup].Children) ON ROWS
FROM
Sales

And it gives us the following grid:

Month ProductGroup Amount
Jan Group1 10
Jan Group2 30
Jan Group3 15
Feb Group1 13
Feb Group2 17

What we'd like is that the ProductGroup level is sorted by amount, while the
month level remains in its original order.

We've tried the following, but of course the hierarchy is broken across all
dimensions and not only the ProductGroup dimension:
SELECT
[Measures].[Amount] ON COLUMNS,
NON EMPTY ORDER(CROSSJOIN([Time].[2003].Children, [ProductGroup].[All
ProductGroup].Children), ([Measures].[Amount]), DESC) ON ROWS
FROM
Sales

We would like it to produce the following grid:

Month ProductGroup Amount
Jan Group2 30
Jan Group3 15
Jan Group1 10
Feb Group2 17
Feb Group1 13

We've been fooling around with the "expression" of the order function with
no success, and we've also tried to put the order function around the
[ProductGroup].[All ProductGroup].Children part of the axis specification
but then nothing happens at all.

Does anyone know if this is possible or if there is a workaround?

Lars-Erik Aabech

Wouldn't that be nice?

That's exactly what I thought, but the result of the query is still
unordered if we do it.
We must be overlooking something..

Here's the exact query with norwegian names, but it should be relatively
easy to understand:
SELECT
{[Measures].[Belop]} ON COLUMNS,
NON EMPTY CROSSJOIN([Ordredato_mnd].[All Ordredato_mnd].[2003].Children,
ORDER([Produktnavn].Children, [Measures].[Belop], DESC)) ON ROWS
FROM
Salg

(Belop = Amount, Ordredato_mnd = Month, Produktnavn = Product, Salg = Sales)

It almost seems like the order function is ignored.. No syntax error, but
the list is unordered.

L-E


"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote

You're correct
It appears that the second set is evaluated once and then duplicated for each member of the first set
This syntax seems to work, there might be other alternatives

SELEC
{[Measures].[unit sales]} ON COLUMNS
NON EMPTY generate( [Time].[1997].Children ,CROSSJOIN( {time.currentmember},order( product.Children ,[measures].[unit sales],desc) )) ON ROW
FROM Sale

HTH
Brian

Hmm.. it works Thanks!

I would like to know why though.. although I don't expect an answer I
understand.

L-E

"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote

ales],desc) )) ON ROWS