Ranking our Reports

One of the interesting requirement both as a user and developer, is to have rankings worked out in your report.Everyone either in school exams, sports etc. get really excited to know how they fare and what's their rank.

Likewise, as we are human to have that nature instilled as an employee too, we want to know how we perform in our organization.

In this post, we will see how you can create a report ranking for your employees too.

Though, we got a function in MDX called Rank(), its not that easy to get it done.

General syntax for Rank,

Rank(Tuple_Expression, Set_Expression [ ,Numeric Expression ] )

Some theory of this function,

- Rank function determines the one-based rank for the specified tuple

by evaluating the specified numeric expression against the tuple. If a 

numeric expression is specified, the Rank function assigns the same 

rank to tuples with duplicate values in the set. This assignment of the 

same rank to duplicate values affects the ranks of subsequent tuples 

in the set. 

Note: I will take an example from Adventure Works, to illustrate the 

same. 

We have a Customer dimension containing all the customers and 

Internet Order Count values.

Now, how can we assign a rank based on the Internet Order count 

values to each of our customers. We will see that in a moment,

Steps:

•  First of all, we will create an Ordered Set, that will order the customers based on the measure, in this case, Internet Order Count. Though, an ordered set is not required and a simple set will work too.

• Then, we will use our rank function on this set.

Here it is, we have ranked our customers based on the order count.