Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm hoping to add a calculation to my SSAS Cube (SSAS 2012) that will show the relative rank of whatever the current member is against all other members in that grouper, without specifying the groupers in advance.

For example, say I have a calculation already for Efficiency which is defined as $$/hour.

I would like to add an Efficiency Rank calculation as well that will tell me what relative position the current member has in it's set.

The Syntax I tried was

RANK([Measures].[Efficiency].CurrentMember, [Measures].[Efficiency])

Which I realize is incorrect.

I can't seem to locate a guide to this anywhere else, so I'm hoping there is some established best practice or pattern for this since it seems like it would be a relatively common business requirement.

share|improve this question
add comment

1 Answer

Please explain in more detail what you meany by "groupers"?

For instance, if you want to rank a member within a set, you need to tell SSAS what the set is that you are ranking within.

RANK( Tuple, Set, Measure to rank by)

So

RANK( Machines.Machines.CurrentMember, Machines.Machines.AllMembers, Measures.Efficiency)

Now, I think that what you are trying to do is have that all members be dynamic?

The question is "How would you both specify the current member and the set?"

And here's a kicker: the frontend tool you're using will make a difference here as well

share|improve this answer
    
This is a good start, thanks! The front end will be either and Excel pivot table or SSRS reports. –  JNK Dec 13 '12 at 15:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.