MS SQL Server OLAP

Author

Item rank

Polly

2005-11-23, 7:24 am

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

Deepak Puri

2005-11-23, 8:24 pm

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:
[color=darkred]
With Member [Measures].[ItemRank] as
'Rank(Axis(0).Item(0).Item(0).Dimension.CurrentMember,
Order(Axis(0).Item(0).Item(0).Dimension.CurrentMember.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[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Polly

2005-11-23, 8:24 pm

Dear Deepak,
Thanks for your reply. Actually, I want to select something like this:

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[color=darkred]

I donno how to write the calculated member [Measures].[ItemRank] which is
based on corresponding month's unit sales.

Regards,
Polly

"Deepak Puri" wrote:
[color=darkred]
> 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:
>
> With Member [Measures].[ItemRank] as
> 'Rank(Axis(0).Item(0).Item(0).Dimension.CurrentMember,
> Order(Axis(0).Item(0).Item(0).Dimension.CurrentMember.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.droptable.com ***
>

Deepak Puri

2005-11-24, 3:23 am

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).
[color=darkred]
With
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.CurrentMember,
[ColumnSet])'
Member [Measures].[ItemRank] as
'Rank(Axis(1).Item(0).Item(0).Dimension.CurrentMember,
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[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Polly

2005-11-24, 3:23 am

Thanks for your solution.

"Polly" wrote:

> 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
>
>

Sponsored Links