Using MDX to Find the Name of a Child with the Best/Worst Value

Andrew Smith Posted on

In their book MDX with SSAS 2012 Cookbook, Sherry Li and Tomislav Piasevoli include a pattern for finding the name of a child with the best or worst value for a given measure.

In this post I work through that pattern, including implementing the variations & extensions that they suggest.

The pattern is one of their “Concise Reporting” patterns, all of which use MDX to home in on key data that’s important to the user, as opposed to presenting them with a huge results set and expecting them to wade through it themselves.

I’ve chosen a different example data set from the book, here implementing MDX that finds the best City (in terms of Internet Sales) for each State-Province in the AdventureWorks cube:

WITH
MEMBER [Measures].[Best Child] AS
    iif(IsLeaf([Customer].[Customer Geography].CurrentMember), null,
        iif(IsEmpty([Measures].[Internet Sales Amount]), null,
            TopCount([Customer].[Customer Geography].CurrentMember.Children, 1,
                       [Measures].[Internet Sales Amount]).Item(0).Name))

SELECT { [Measures].[Internet Sales Amount], [Measures].[Best Child] } ON 0,
NON EMPTY { Descendants( [Customer].[Customer Geography].[Country], 1, SELF) } ON 1
FROM [Adventure Works]

A sample of the data returned is:

mdxbestchildqueryresults

So looking at the calculated measure in the MDX above, the initial iif() statement is checking whether or not we’re at a leaf, since we need to access the children of the current member and this makes no sense if we are at a leaf. (In this particular example the descendant members of [Customer].[Customer Geography].[Country] returned from the State-Province level will always have children.)

The second iif() statement checks whether the cell for the [Internet Sales Amount] measure at the current state-province aggregate is empty. Doing this check and returning null if it is, allows the NON EMPTY keywords on the row axis to work, since if the [Internet Sales Amount] measure is null then the calculated measure should also be null and if both are null then the whole row will be filtered out by NON EMPTY.

The last part of the calculated measure is then the TopCount() function, which runs over the cities (i.e. the children) of the current state-province member and which will return the single child with the highest [Internet Sales Amount] value. TopCount() always returns a set, so Item(0) is used here to extract the member and then the Name intrinsic member property is extracted from that.

The results can be checked by comparing them with a query that lists sales for all cities for each state-province:

WITH
MEMBER [Measures].[State-Province] AS
    iif(IsEmpty([Measures].[Internet Sales Amount]), null,
         [Customer].[Customer Geography].CurrentMember.Parent.Name)

SELECT { [Measures].[State-Province], [Measures].[Internet Sales Amount] } ON 0,
NON EMPTY { Descendants( [Customer].[Customer Geography].[Country], 2, SELF_AND_BEFORE) } ON 1
FROM [Adventure Works]

and except of which is:

mdxbestchildqueryresultscheck

As Li and Piasevoli note, the pattern can be used to find the worst child also, but taking care in this case that the NonEmpty() function is applied first to ignore empty values. So the TopCount() function from the [Best Child] calculated measure above is replaced with:

BottomCount(NonEmpty([Customer].[Customer Geography].CurrentMember.Children, [Measures].[Internet Sales Amount]), 1, [Measures].[Internet Sales Amount]).Item(0).Name))

So children with an empty value for [Internet Sales Amount] are eliminated, leaving BottomCount() to only run over cities with non-empty sales.

As an aside, the results of sorting within TopCount() and BottomCount() are grouped as follows (from highest to lowest):

1. Positive values
2. Zero values
3. Null values
4. Negative values

So nulls aren’t at either one end of the numeric spectrum or the other.

Finally, Li and Piasevoli suggest that if more than one name is required within a cell, then the Generate() function can be used to concatenate them. I’ve implemented this around the TopCount() function from the first calculated measure above as follows:

Generate(TopCount([Customer].[Customer Geography].CurrentMember.Children, 2, [Measures].[Internet Sales Amount]), [Customer].[Customer Geography].CurrentMember.Name, “, “)))

So Generate() here is being used in its string-with-delimiter form and where I now have TopCount() returning the top 2 cities by sales. Example output is as follows:

mdxbestchildqueryresultstwo

The basic pattern used in this post is simple and can clearly be used as the basis of many variations. Together with the other patterns from the same chapter of Li and Piasevoli’s book, it can be used to help focus MDX development on returning data to users in the most effective way.