May 9, 2011 at 2:23 am
Hi all
I have been scratching my head for some time now about this little problem regarding Raking within a Dimension Hierarchy.
I have a Dimension called Entity. This Dimension has a ROWID, an Organisation Name, a Team Name and a User Name.
I have created the following relationship:
ROWID -> User Name -> Team Name -> Organisation Name
I have created the following Calculations in my Cube:
[Rank by Volume] (ranking of a User within the context of ALL the users regardless of Organisation or Team)
Iif ( IsEmpty ([Measures].[Volume]), NULL,
Rank
(
[Entity].[Organisation - Team - User].CurrentMember,
[Entity].[Organisation - Team - User].[User Name].Members,
[Measures].[Volume]
))
[Rank by Volume within Team] 9ranking of a user within its own Team)
Iif ( IsEmpty ([Measures].[Volume]), null,
Rank(
[Entity].[Organisation - Team - User].CurrentMember,
ORDER ([Entity].[Organisation - Team - User].CurrentMember.Siblings,
[Measures].[Volume], BDESC)
))
My problem is that I cannot find a way to create the Raking Calculation for a User within an Organisation.
Also if someone has a better way to create the Raking Calculations I'd be more than happy to hear about it and extend my knowledge of SQL Server.
Thanks!
Ludo
May 9, 2011 at 3:34 am
Hi
Sorry to answer my own post but I have found the correct solution:
Iif ( IsEmpty ([Measures].[Volume]), null,
Rank(
[Entity].[User Name].CurrentMember,
Exists ([Entity].[User Name].[User Name],
{[Entity].[Organisation Name].CurrentMember}),
[Measures].[Volume]
))
Also if I want to see the ranking within a Team, the Calculation becomes:
Iif ( IsEmpty ([Measures].[Volume]), null,
Rank(
[Entity].[User Name].CurrentMember,
Exists ([Entity].[User Name].[User Name],
{[Entity].[Team Name].CurrentMember}),
[Measures].[Volume]
))
Simple!
I hope this may help other SSAS Developpers.
Cheers
Ludo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply