Re: MDX order() confusion

#1

Thanks for your reply.

Let me explain the problem conceptually instead of by MDX, maybe it will be
clearer then.
In my client i allow the user to put dimension(s) on two axes (crossjoined
if > 1 dimension is chosen), and order these according to some measure. For
our users the logical thing, when they choose to order a set in the client
(ex: the salespersons on the rows), is that these are ordered in relation to
whats on the columns. So, if they choose [Year].Members they want the rows
to be ordered by Sales (what i call value) for all years. If they choose a
single member from the year level, they want it to be ordered by the Sales
for that year only, and finally if they choose a set of years (like {[2002],
[2003]} they want it ordered by the sales for those years.

The result should look like:
2001 2002 2003
Henry Miller 100 200 300
Cathy Owens 90 33 33
Mark Jacobs 10 2 13
.....

Any further suggestions?

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> skrev i melding
news:umRc$9CSDHA.2676 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

Quote:

Each axis of the MDX query is evaluated independently, I think, so the
specific tuple on the column axis in your first query (Year 2003) does
not establish a context for computing the row axis tuple set for
[SalesPerson].

What do you want the row axis set of [SalesPerson] members to be ordered
by, when you have multiple [Year] members on the columns axis? If you
need the order for each [Year] individually, then you can cross-join the
[Time] and [SalesPerson] dimensions, creating a list for each year:

Select {[Measures].[Value]}
on Columns,

Generate([Time_YM].[Year].Members,
CrossJoin({[Time_YM].CurrentMember},
ORDER([SalesPerson].[SalesPerson].Members,
([Measures].[Value],[Time_YM].CurrentMember), BDESC)))
on Rows

From Sales

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#2

With reference to ths sample results you showed, there is a basic
question: what if Henry Miller has the highest Sales in 2001, but Cathy
Owens tops in 2002 and so on? How should the results table look then?
The sample results seem to show that the Salespeople are ordered the
same each year.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#3

The ordering should be based on the sum of "Value" for the selected years.
As in:
2001 2002 2003 **ORDERED BY**
Henry Miller 100 200 300 600 (100 + 200 + 300)
Cathy Owens 90 33 33 156 (90 + 33 + 33)
Mark Jacobs 10 2 13 25 (10 + 2 + 13)

- Peter

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> skrev i melding
news:elTRm0jSDHA.2128 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

Quote:

With reference to ths sample results you showed, there is a basic
question: what if Henry Miller has the highest Sales in 2001, but Cathy
Owens tops in 2002 and so on? How should the results table look then?
The sample results seem to show that the Salespeople are ordered the
same each year.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#4

Then how about an MDX query something like this:

Quote:

SELECT {[Time_YM].[All Time_YM].Children} ON COLUMNS, ORDER

([SalesPerson].[SalesPerson].members, Sum(Axis(0),[Measures].[Value]),
BDESC) ON ROWS FROM Sales

Quote:

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#5

I did not know one could refer to axes as sets! This is exactly what i need.
Thanks alot!

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> skrev i melding
news:uun$lYqSDHA.2480 (AT) tk2msftngp13 (DOT) phx.gbl...

Quote:

Then how about an MDX query something like this:

SELECT {[Time_YM].[All Time_YM].Children} ON COLUMNS, ORDER
([SalesPerson].[SalesPerson].members, Sum(Axis(0),[Measures].[Value]),
BDESC) ON ROWS FROM Sales

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: MDX order() confusion

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: MDX order() confusion - 07-14-2003 , 01:48 AM

Re: MDX order() confusion - 07-14-2003 , 02:21 PM

Re: MDX order() confusion - 07-15-2003 , 01:35 AM

Re: MDX order() confusion - 07-15-2003 , 02:53 AM

Re: MDX order() confusion - 07-16-2003 , 02:24 AM