I have a simple cube with a Store dimension with levels:

Region
State
City
Store

A single measure Sales.

Want MDX to see State on Rows and columns for:

Total Sales: Total sales for the State
Top Store: Name of the Store in the State with highest Sales

I can get the Sales column easy enough,
but how to get the name of the top store per State?

Thanks,

LMcPhee

Here's an MDX query for the Foodmart Sales cube, which you can try in
the MDX Sample App:

'TopCount(Descendants([Store].CurrentMember,
[Store].[Store Name]),
1, [Measures].[Unit Sales]).Item(0).Item(0).Name'

Select {[Measures].[Unit Sales],
[Measures].[TopStore]} on columns,
NonEmptyCrossJoin([Store].[Store State].Members) on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***