Few months ago, while on vacation, I saw an interesting post on MSDN forum where a user asked for rank of members placed on rows, irrespective of hierarchy. Furthermore, that was supposed to be achieved using only one calculated measure, nothing else. Intrigued by this puzzle, I made an example of such calculated measure on Adventure Works database using Axis() function. Here it is:
WITH
MEMBER [Measures].[Sales Amount Rank] AS
iif(
IsEmpty([Measures].[Sales Amount]),
null,
Rank(
Axis(1).Item(0).Item(Axis(1).Item(0).Count – 1
).Hierarchy.CurrentMember,
Order(
Extract(
Axis(1),
Axis(1).Item(0).Item(
Axis(1).Item(0).Count – 1
).Hierarchy
),
[Measures].[Sales Amount],
BDESC
)
)
)
SELECT
{
[Measures].[Sales Amount],
[Measures].[Sales Amount Rank]
}
ON AXIS(0),
NON EMPTY
[Product].[Category].[Category].MEMBERS *
[Sales Territory].[Sales Territory Country].
[Sales Territory Country].MEMBERS
ON AXIS(1)
FROM
[Adventure Works]
Here’s how it works.
We should test for empty values first. Empty values of the measure on which we do ranking (Sales Amount in this example). This is done in order not to influence the original result, the one without the rank measure. Why? Because Rank() function returns numbers, never nulls. Normally, NON EMPTY serves to filter rows for which Sales Amount in the above example is null. If we introduce the second measure that is never null, those rows will suddenly appear too. And this is not a wanted behavior.
Rank is made by using current member of the last hierarchy on rows, as requested. Ordered set is made by extracting that hierarchy and ordering its members by measure Sales Amount. The trick is that Order() function returns a different set of members for each row. In other words, current context (tuple) influences ordering of members. On each Category (first hierarchy), another ordered set of Countries (last hierarchy) might appear, because countries will be ordered per current product category. The same would work for any number of hierarchies on rows.
Recently, I was asked to make it even more universal (general) in a way to avoid ranking of members from different hierarchy levels at the same time. Meaning, if we place .ALLMEMBER, we don’t want [All] member to be ranked as 1, while all the others as second, third, and so on.
Another puzzle, another solution. This time I named it Goldfish Rank since it fulfills any wishes you might have about ranking in general. Here it is:
WITH
MEMBER [Measures].[Goldfish Rank] AS
iif(
IsEmpty(Axis(0).Item(0).Item(0))
— making it absolutely general
— using first measure on columns
/*
— un-comment this block if you want to get rank
— only for lowest level members in you last column
OR
Axis(1).Item(0).Item(Axis(1).Item(0).Count – 1
).Hierarchy.CurrentMember.Level.Ordinal <
Max(
Extract(
Axis(1),
Axis(1).Item(0).Item( Axis(1).Item(0).Count – 1
).Hierarchy
),
Axis(1).Item(0).Item(Axis(1).Item(0).Count – 1
).Hierarchy.CurrentMember.Level.Ordinal
)
*/
,
null,
Rank(
Axis(1).Item(0).Item(Axis(1).Item(0).Count – 1
).Hierarchy.CurrentMember,
Intersect(
Order(
Extract(
Axis(1),
Axis(1).Item(0).Item(
Axis(1).Item(0).Count – 1
).Hierarchy
),
Axis(0).Item(0).Item(0),
— making it absolutely general
DESC
— BDESC would be redundant,
— since we work per level
),
iif(
Axis(1).Item(0).Item(
Axis(1).Item(0).Count – 1
).Hierarchy.CurrentMember
.Level.Ordinal = 0,
Axis(1).Item(0).Item(
Axis(1).Item(0).Count – 1
).Hierarchy.CurrentMember,
NonEmpty(
Axis(1).Item(0).Item(
Axis(1).Item(0).Count – 1
).Hierarchy.CurrentMember
.SIBLINGS,
{ Axis(0).Item(0).Item(0) }
— making it absolutely fast
)
)
*/
— /*
— this is the second block, referred above
— comment NonEmpty() function
— if you un-comment the block above
NonEmpty(
Axis(1).Item(0).Item(
Axis(1).Item(0).Count – 1
).Hierarchy.CurrentMember
.Level.MEMBERS,
{ Axis(0).Item(0).Item(0) }
— making it absolutely fast
)
— end of second block
— */
)
)
)
SELECT
{
[Measures].[Sales Amount],
[Measures].[Goldfish Rank]
}
ON AXIS(0),
NON EMPTY
{
[Product].[Category].ALLMEMBERS
* [Sales Territory].[Sales Territory].ALLMEMBERS
}
— un-comment lower part and comment the line above
— if you find it difficult to compare results
— when the last column is hierarchized
— and when applying rank on level members, not siblings
/*
*
{
[Sales Territory].[All],
[Sales Territory].[Group].MEMBERS,
[Sales Territory].[Country].MEMBERS,
[Sales Territory].[Region].MEMBERS
}
*/
ON AXIS(1)
FROM
[Adventure Works]
This query has [All] members on rows. From both hierarchies. That serves for testing purposes of newly created calculated measure for rank.
Calculated measure used in previous example was enhanced in a way that it sorts only members that have the same level (or parent) as current one (current member from the last hierarchy). This is achieved using Intersect() function. Members on each level (or siblings, depends on the switch you make with blocks of code) will be ranked only according to equal to themselves. By the term equal, I assume those from the same level (or of the same parent). Metaphorically, having the same chance to be ranked as 1.
Second argument of Intersect() function should have been simple .MEMBERS from particular level (or .SIBLINGS), but that was optimized using NonEmpty() function and the same measure we rank by. Optimization means we reduce potentially large set of members by applying NonEmpty() function on them, so that only those having result for that measure will survive.
Goldfish Rank has several options how to deal with higher levels.
First commented block enables user to rank only on lowest level members, while providing nulls for the rest. The second block enables user to avoid ranking the [All] member, since it will most probably always have rank 1. And finally, if you find it hard to match members from different levels in the example above, I provided commented block of several levels for the second user hierarchy on axis 1. This way results will not be hierarchized, so it will be easier for you to compare them and see them in action.
Another way of ranking is to rank among siblings. For that purpose, I provided a commented block inside Intersect() function. Using that instead of the block below (un-commented) will give us results as in right picture.
While there can be other variations of ranking, I assume the code above will suffice as a template for each of those variations. If you encounter any problems during that, remember to ask the Goldfish!
In the end, it’s worth saying that the forum user’s wish was fulfilled.
– If you like this post, be sure to drop by occasionally, since there will be more interesting MDX queries in the days to come.
