Finding the name of a child with the best/worst value

Sometimes there is a need to perform a for-each loop in order to get the top or bottom members in the inner hierarchy for each member in the outer hierarchy. The recipe Identifying the best/worst members for each member of another hierarchy deals with exactly that kind of topic.

Following the theme of the recipes in this chapter to reduce the size of the report to a manageable level, in this recipe, we will show another possibility for reducing the size of the result, by showing not all the descendant members, but only the best child member. We'll demonstrate how to identify the member with the best/worst value, only this time the member is not just from any other hierarchy, it will be from its children. We actually do not need the best/worst value from the child, and we are only going to return the name of the child in a calculated measure.

In our example, we will use the Product dimension. For every product subcategory, we are going to...