Thursday, January 24, 2013

MDX Running totals on a nested ordered set

I like MDX puzzles, and just got a good one today.  The requirement was to have running totals over an ordered set, but the set was only ordered at the lowest level, and the running totals were just within the parent member.  Ie, a list of countries and cities, where cities are ordered descending by [net sales] within country, and [profit] has a running total for the cities within that country.

As I have said before, anything is possible with MDX, and here is the solution for ordering cities within countries and having running totals of profit of cities within countries.  I haven't ordered the countries here, but they could be independently ordered.

Essentially, what the query does is order the cities (along with city.All).  For the running total it takes the members from the City.All member to the current member and aggregates them.  Simple really.

with
set OrderedCities as
[Geography].[Country].[Country]
*Order(Nonempty([Geography].[City].Members,[Measures].[Profit])
,[Measures].[Net Sales], DESC)  
 
member [Measures].[CityRank] as rank(([Geography].[Country].CurrentMember,[Geography].[City].currentmember),OrderedCities)
member [Measures].[CountryRank] as rank(([Geography].[Country].CurrentMember,[Geography].[City].[All]),OrderedCities)

member [Measures].[ProfitRunningTotal] as
sum( 
Topcount(OrderedCities-TopCount(OrderedCities, ([Measures].[CountryRank]))
,[Measures].[CityRank]-[Measures].[CountryRank])
,[Measures].[Profit]) 

select 
{[Measures].[Net Sales]
,[Measures].[CityRank]
,[Measures].[CountryRank]
,[Measures].[Profit]
,[Measures].[ProfitRunningTotal]
} on columns,

OrderedCities  on rows

from [MyCube]

No comments: