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.

with
member measures.mc as [Client Host].[Client Geography].member_caption
set MyCities as Order(nonempty([Client Host].[Client Geography].City,Measures.Hits)
,[Client Host].[Client Geography].member_caption,BASC)
member measures.r as rank([Client Host].[Client Geography],MyCities)
member measures.CityTot as iif((MyCities.item(Measures.r-2),Measures.mc)=[Client Host].[Client Geography].member_caption,
Measures.Hits+(Measures.CityTot,MyCities.item(Measures.r-2)),
Measures.Hits)
member Measures.CityTotal as
iif((MyCities.item(Measures.r),Measures.mc)=[Client Host].[Client Geography].member_caption,
null,Measures.CityTot)
select {Measures.CityTotal} on 0,
non empty
MyCities on 1
from EasternMining


You can slice and dice the real data on http://RichardLees.com.au/Sites/Demonstrations