Is it possible to do a topcount or an order on two measures ?
In T-SQL we do an "order by criteria1,criteria2 etc etc"
I need to sort a set with two measures and i don't know how to do.

A good example : i do a top 30 product family on a sales cube, but i want to
see the product that have 0 in sales and have sales in the past year
So i would like to so an order by Measures.Amount,Measures.AmountY-1

Thanks a lot

Here's a Foodmart Sales MDX query that effectively orders Product
Categories by [Store Sales], [Sales Q-1]. The outer Order() won't
re-order tuples with the same sort value:

'([Measures].[Store Sales], ParallelPeriod([Time].[Quarter]))'
Select {[Measures].[Store Sales], [Measures].[Sales Q-1]} on columns,
NON EMPTY Order(Order([Product].[Product Category].Members,
[Measures].[Sales Q-1], BDESC),
[Measures].[Store Sales], BDESC) on rows
from Sales
where ([Time].[1997].[Q3],[Customers].[All
Customers].[USA].[OR].[Portland] )

- Deepak

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

If i understand what you say to do a topcount on two measures i must do a

head(order(order({myset},MySecondOrderCriteria,des c),MyFirstOrderCriteria,de
sc),mytopnumber)

Is it correct?
my set is a result of a crossjoin so i want to keep the hierarchie
Thanks for your answers.



"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

That sounds right, but I haven't tested with DESC flag.


- Deepak

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