Archive for May, 2010

Rank Function (For same value)

May 25, 2010

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.


Design a site like this with WordPress.com
Get started