Rank Function (For same value)

As we all are aware of rank function. It gives incremental rank for selected set.
I am going to discuss a silent feature of rank which many are not aware since now.



WITH SET [ProductSet]
AS
ORDER ([Product].[Product].[Product].Members, [Measures].[Internet Sales Amount],DESC)
MEMBER [Measures].[Rank] as
RANK([Product].[Product].CurrentMember, [ProductSet])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Rank]} ON 0 ,
[ProductSet] ON 1
FROM
[Adventure Works]
WHERE
[Date].[Calendar Year].&[2002]


Now see at rank 2 & 3, both has same Internet Sales Amount, but they are ranked as 2 and 3.
But our business logic needs both should have same rank since both have same sales Amount.
So in Analysis Service 2005 its possible by adding argument at rank function.
Lets see


WITH SET [ProductSet]
AS
ORDER ([Product].[Product].[Product].Members, [Measures].[Internet Sales Amount],DESC)
MEMBER [Measures].[Rank] as
RANK([Product].[Product].CurrentMember, [ProductSet],[Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Rank]} ON 0 ,
[ProductSet] ON 1
FROM
[Adventure Works]
WHERE
[Date].[Calendar Year].&[2002]

Now we can see that , its prefect result.

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts