MDX - Aggregating by member_caption

Here is an interesting MDX puzzle. Try aggregating a dimension hierarchy by member captions, where the members have unique keys, either by different hierarchies or just different keys. When you try to sum() a set, you have a tendancy of losing the position of the member on the axis.

Here is a solution to the puzzle using recursive MDX. If the preceding member has the same name, it moves to the preceding member and adds the measure. I have used the client geography dimension in my Weblogs cube. There are many city names that exist in multiple states and/or multiple countries. See 'Springfield' appears in 7 states. The challenge is to get the sum of all distinct city names. I know this is not a likely query, but if it was a customer name that was in many states, it might be a real query.

Read more...