Dear all,

I want to make a calculated member to show the rank of item based on sales
figures. For example, if I choose July and August in Time dimension on column
and all items on row, the calculated member should show item rank for July
and August. July rank and August rank should be based on July sales and
August sales respectively.

I have tried the following MDX,
rank({[Item].members}, [Measures].[Sales]).

However, it is not what I want. Item ranks for July and August are the same.
I guess the value is the item rank based on sum of July and August Sales.

Can anyone suggest me a solution of the above problem? Thanks!

Regards,
Polly

When you say "all items on row", it's not clear what these items are -
since July and August are already on columns. But here's a sample
ranking query for Foodmart Sales:

'Rank(Axis(0).Item(0).Item(0).Dimension.CurrentMem ber,
Order(Axis(0).Item(0).Item(0).Dimension.CurrentMem ber.Level.Members,
Axis(1).Item(0), BDESC), Axis(1).Item(0))'
Select {[Time].[1997].[Q3].[7], [Time].[1997].[Q3].[8]} on columns,
{[Measures].[Unit Sales], [Measures].[ItemRank]} on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Dear Deepak,
Thanks for your reply. Actually, I want to select something like this:
[Time].[1997].[Q3].[8]},{[Measures].[Unit Sales], [Measures].[ItemRank]}) on
columns,
[Product].[Product Name].members on rows
from Sales
based on corresponding month's unit sales.

Regards,
Polly


"Deepak Puri" wrote:

Hi Polly,

Based on your example, the problem is slightly different than I thought.
Accordingly, here's a sample solution, with some assumptions/comments:

- It assumes that there is a single dimension preceding [Measures] on
columns, one measure preceding [ItemRank] and one "item" dimension on
rows.

- The reason for using the [OrderedItems] set is to "cache" the ordered
items once, thereby avoiding repeated sorts (this greatly reduced query
time, but adds complexity).

Set [ColumnSet] as
'Extract(Axis(0), Axis(0).Item(0).Item(0).Dimension)'
Set [ColumnMeasures] as
'Extract(Axis(0), [Measures])'
Set [OrderedItems] as
'Generate([ColumnSet], Order(Axis(1),
([ColumnSet].Current.Item(0),
[ColumnMeasures].Item(0).Item(0)),
BDESC), ALL)'
Member [Measures].[ColumnRank] as
'Rank(Axis(0).Item(0).Item(0).Dimension.CurrentMem ber,
[ColumnSet])'
Member [Measures].[ItemRank] as
'Rank(Axis(1).Item(0).Item(0).Dimension.CurrentMem ber,
Subset([OrderedItems],
Axis(1).Count * ([Measures].[ColumnRank] - 1),
Axis(1).Count),
[ColumnMeasures].Item(0))'

Select CrossJoin({[Time].[1997].[Q3].[7],
[Time].[1997].[Q3].[8]},{[Measures].[Unit Sales],
[Measures].[ItemRank]}) on
columns,
[Product].[Product Name].Members on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for your solution.

"Polly" wrote: