I am trying to achieve tied ranking in MDX. It works fine with the
columns whose values are just integers.
e.g. If I rank by 'Value' Field and also set third argument 'Numeric
Expression' for Rank() function as 'Value' Field only i get the
following results.

Desc Value Rank
-------------------------------------------
Item 1 10 1
Item 2 15 2
Item 3 15 2
Item 4 20 4


But having problems when trying to rank on Fields whose values have
decimals on it. I have a calculated member 'CalcValue' in my Cube which
is the result of a division of two members. So I set it's Format String
as '##.0%' so that it's nicely formatted as Percentage for presentation
on the report.

e.g. If I rank by 'CalcValue' Field and also set third argument
'Numeric Expression' for Rank() function as 'CalcValue' Field, i get
the following results.


Desc CalcValue Rank
-------------------------------------------
Item 1 10.6 1
Item 2 15.4 2
Item 3 15.4 3
Item 4 20.5 4

So the tied ranking is not working as expected.

I am wondering what does Rank() function in AS2005 take into
consideration when trying give rank numbers. Is it the value before
formatting with lot of decimal places or the value after formatting. If
it is the value after formatting it should work in my case. I suspect
it is taking the values before formatting and obiviuosly they are not
equal before formatting to get the same ranks in tied ranking.

Did anyone notice such behaviour. Any help is appreciated.

Regards
Raj Chidipudi

I understand formatted value is just for display. It is not real value.

Ohjoo

"Raj Chidipudi" <chidipudi (AT) gmail (DOT) com> wrote

I think you are right Ohjoo.

Looks like AS2005 uses only the actual results of the calculations for
ranking. I had to write a custom library function to calculate and
format ''CalcValue' first then apply rank() function on it.

Raj

Ohjoo Kwon wrote: