Hi,
I am trying to create a calculated measure that rank customers based charge
amount. I have created a Name Set and Calculated Measure as followed:

Top10 = TopCount([CUSTOMER].Members, 10,([CHARGE TYPE].CurrentMember,
[Measures].[Charge Amt]))

Customer Rank = Rank([CUSTOMER].CurrentMember, {[Top40]},([CHARGE
TYPE].CurrentMember,[Measures].[Charge Amt]))

I have created an PivotTable with the following dimensions and measures:

PageFilter
-----------
OFFICE
DESK
PERIOD
CHARGE TYPE(GROSS COMMISSIOIN, LOCAL BROKER FEE, US CLEARING FEE, ETC.)

ROW
-----
CUSTOMER

COLUMN
-----------
CHARGE AMT
CUSTOMER RANK

Here is my result:

CUSTOMER CHARGE AMT CUSTOMER RANK
-------- ------------ ------------
C2350021 608,093.04 1
C4525045 518,528.37 1
C9034850 566,929.96 1
C1256340 493,519.20 1
C3456569 1,625,785.03 2
C7694950 1,470,338.30 3
C9476583 1,284,734.32 4
C3487900 1,205,712.97 5
C0983745 1,117,468.76 6
C1289340 893,224.21 7
Grand Total 9,784,334.16 1

Not sure why I am getting customer with the same ranking. Also, when I
change the charge type selection, the ranking does not change. I need to be
able to select different charge type and display the ranking.

Any assistant is greatly appreciated.

Regards,
Fernando Sanchez

Hi Fernando,


Here are some possible issues with your approach:

- A cube or session Named Set will not be dynamically recomputed each
time the charge type selection changes.

- Maybe you're including customers that aren't in the Top40 Named Set,
so they're all assigned a rank of 1.


This recent thread discusses a solution, though there may be performance
problems when the row set is large:

http://groups.google.com/group/micro...olap/browse_fr
m/thread/51673105feb39eaf/4b8c06c0fc689ac9#4b8c06c0fc689ac9
From: "alanr"
Date: 13 Oct 2005 09:06:25 -0700

Subject: visual rank calculated member on server is very very slow

Greetings!

I am struggling with the performance of a visual ranking calculation
defined on the server. I based this on posts from Tom Chester and
others. I am using the September CTP (which as a side note seems to
have eliminated the need to do order the set before ranking?). Excel
2003 pivot table is client. Note that the member won't work with OWC
because the axis doesn't have the measure on it.

For very small dimensions it is just fine but for those with even a few
hundred members it takes more than a minute. With a thousand members
takes 10+ minutes. Ugh This is probably because it is performing
the rank calculation for each cell and nothing is cached between cells.

I have seen some very interesting posts from Deepak that suggest how to
cache ordered sets using "WITH" and Generate but I don't think there is
a way to do that within a server calculated member? Also, I don't want
to cache the ordered set, I want to cache the RANK with it's elegant
handling of ties.

Here is the definition of the member

CREATE MEMBER CURRENTCUBE.[MEASURES].[Rank Desc]
AS
-- IF GRAND TOTAL THEN NULL
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension. CurrentMember.Level
IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.Defa ultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0)
.Co*unt
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Ax is(1)").Item(0).Count
-1).Dimension.CurrentMember,
StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1) ").Item(0).Count
-1).Dimension.CurrentMember.Siblings,
-- RANK DESC
-1 * StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0) ").Item(0).Count
-1)))),
VISIBLE = 1
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,
Thanks for responding...I am not sure how to go about creating this
calculated measure because of the command. Is there a utility that I can use
to execute this command?

Regards,
Fernando Sanchez

"Deepak Puri" wrote:

If you're using AS 2000, you can create a new calculated member in
Analysis Manager, with MDX definition as:

IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.Defa ultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0)
.Co*unt
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Ax is(1)").Item(0).Count
-1).Dimension.CurrentMember,
StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1) ").Item(0).Count
-1).Dimension.CurrentMember.Siblings,
-- RANK DESC
-1 * StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0) ").Item(0).Count
-1))))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,
I was able to created the calculated measure below; however, I had to remove
the dash from Co-unt and it compiled. Thanks again for your help and
guidance.

Regards,
Fernando Sanchez

"Deepak Puri" wrote:

Hi Deepak,
I posting this again because I believe the first time I responded to your
posting, it did not go through.

I believe I am not getting the correct result from the calculated measure
you recommended. Perhaps, I am not understanding it.

I have the following on my PivotTable:

PageFilter
---------------------------
PERIOD = 2005
OFFICE = ALL OFFICE
DESK = ALL DESK
CHARGE TYPE = GROSS COMMISSION

ROW
-----------
CUSTOMER NAME

COLUMNS
------------
CHARGE AMT
CUSTOMER RANK

Here is my results
------------------------
C001 1,625,785.03 157
C002 1,470,338.30 167
C003 1,284,734.32 351
C004 1,205,712.97 94
C005 1,117,468.76 194
C006 893,224.21 147
C007 608,093.04 254
C008 566,929.96 392
C009 518,528.37 364
C010 493,519.20 393


Customer Rank
------------------
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension. CurrentMember.Level IS
StrToSet("Axis(1)").Item(0).Item(0).Dimension.Defa ultMember.Level, NULL,
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0).Count
-1)), NULL,
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Ax is(1)").Item(0).Count
-1).Dimension.CurrentMember,
StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1) ").Item(0).Count
-1).Dimension.CurrentMember.Siblings, -1 *
StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0) ").Item(0).Count -1))))

Regards,
Fernando Sanchez

"Deepak Puri" wrote:

Hi Fernando,

My guess is that, since you're using AS 2000, Rank() doesn't work quite
the same way; so you need to order the set before ranking. You can add
Order() as below (but you can see from the thread that performance could
be poor):

IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.Defa ultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0)
.Co*unt
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Ax is(1)").Item(0).Count
-1).Dimension.CurrentMember,
Order(StrToSet("Axis(1)").Item(0).Item(StrToSet("A xis(1)").Item(0).Count
-1).Dimension.CurrentMember.Siblings,
-- RANK DESC
StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0) ").Item(0).Count-1),
BDESC),
StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0) ").Item(0).Count-1))))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,
Thanks for the tip...It worked. However, performance is acceptable. I have a
question, can I have more than one measure for columns. For example, I only
want to rank by Charge Type -- Gross Commission -- however, I want to show
Year-To-Date also. Will this work?

Regards,
Fernando Sanchez

"Deepak Puri" wrote:

Hi Fernando,

If you look at the MDX definition, the first tuple on columns is used
for ranking. So, the measure used to rank should be the first on
columns, but others can follow.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Good morning Deepak,
I had a feeling you were going to say that...Anyway, I can live with that or
the user can. I also noticed that the calculated measure works fine when you
add another dimension to the ROW. However, after the second dimension, I get
all ones. Do you know why this happens?

Regards,
Fernando Sanchez

"Deepak Puri" wrote: