Dear all

I have an ranking issue which I would really appreciate
some help on. In Foodmart terms what I would like to see
is a list of all the products being sold and the measure I
would like to have on rows is the rank assoicated with
each product for a given store. I have a query that will
return the rank for one product at a time. The query is

WITH

SET [myset] AS
'
ORDER ([Store].[Store Name].members,([Product].[All
Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Beer].[Good].[Good Imported Beer],[Measures].[Unit
Sales]),BDESC)

'

MEMBER [Measures].[myrank] AS
'
RANK([Store].CurrentMember,[myset],([Product].[All
Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Beer].[Good].[Good Imported Beer],[Measures].[Unit
Sales]))

'

SELECT

{[myrank],[Measures].[Unit Sales]} ON COLUMNS,
{[Product].[All Products].[Drink].[Alcoholic Beverages].
[Beer and Wine].[Beer].[Good].[Good Imported Beer]} ON ROWS
FROM Sales
WHERE ([Store].[All Stores].[USA].[CA].[Beverly Hills].
[Store 6])


What I would like to do is something like this

WITH

SET [myset] AS
'
ORDER ([Store].[Store Name].members,
([Product].CurrentMember,[Measures].[Unit Sales]),BDESC)

'

MEMBER [Measures].[myrank] AS
'
RANK([Store].CurrentMember,[myset],
([Product].CurrentMember,[Measures].[Unit Sales]))

'

SELECT

{[myrank],[Measures].[Unit Sales]} ON COLUMNS,
{[Product].[Product Name].members} ON ROWS
FROM Sales
WHERE ([Store].[All Stores].[USA].[CA].[Beverly Hills].
[Store 6])

But for each product I seem to be getting the same rank.
Cany anyone provide any suggestions on how I might
approach this problem? Any help would really be
appreciated.

Thanks

Harish

I think I have solved my own problem with the following
query

WITH

SET [myset] AS
'
ORDER ([Store].[Store Name].members,
([Product].CurrentMember,[Measures].[Unit Sales]),BDESC)

'

MEMBER [Measures].[myrank] AS
'
RANK([Store].CurrentMember,(ORDER ([Store].[Store
Name].members,([Product].CurrentMember,[Measures].[Unit
Sales]),BDESC)),([Product].CurrentMember,[Measures].[Unit
Sales]))

'

SELECT

{[myrank],[Measures].[Unit Sales]} ON COLUMNS,
{[Product].[Product Name].members} ON ROWS
FROM Sales
WHERE ([Store].[All Stores].[USA].[CA].[Beverly Hills].
[Store 6])

Does anyone have any other suggestions?

Something like this...

WITH MEMBER
[Measures].[Rank] AS
' Rank ( Product.CurrentMember ,
Order(Drink.Children, ([Profit]), DESC) ) '
SELECT
{[Profit] , [Rank]} ON COLUMNS,
Drink.Children ON ROWS
FROM Sales

Substitute your named set for Drink.Children.

tom @ the domain below
www.tomchester.net


"Harish Rao" <h.rao (AT) nopworld (DOT) com> wrote