Hi,

I'am trying to create a query somewhat similar to that shown in this
thread:
http://groups.google.com/group/micro...b863fdd bdb77

I will paste the MDX here:

WITH
SET Top5Cities AS
' TOPCOUNT( [Store].[Store City].Members , 5,
[Measures].[Unit Sales] )'

MEMBER Store.Other AS
' (Store.DefaultMember, [Unit Sales]) -
Sum([Top5Cities], [Unit Sales]) '

SELECT { [Measures].[Unit Sales] } on COLUMNS,
{ [Top5Cities], {Store.Other} } ON ROWS
FROM Sales


The idea is to get the same query but for each country in a countries
top 5. The result should be something like this:

Countries Sales
-----------------
Country1
City1 Sales
City2 Sales
...
Other Sales
Total Sales
Country2
City3 Sales
City4 Sales
...
Other Sales
Total Sales


Is this possible to achieve with MDX?

Thank you,

Peter.

Hi Peter,

Does this query help - it uses Top 2, since Oregon has only 2 store
cities, and states instead of countries?

SET [StateTop2Cities] AS
'Generate([Store].[All Stores].[USA].Children,
{{TOPCOUNT([Store].Children, 2, [Measures].[Unit Sales]) as TC},
VisualTotals({[Store].CurrentMember, [Store].Children - TC},
"* - Other") - [Store].Children, [Store].CurrentMember})'

SELECT {[Measures].[Unit Sales]} ON COLUMNS,
[StateTop2Cities] ON ROWS
FROM Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

Thank you very much for your reply. The query you have provided seems
to do the trick. I am curious if a third level can be supported? In
other words top N Countries,top N States and then top N Cities, with
totals and subtotals? What about a general use case with 3 to 5 levels?
Is there a general solution to this?

Thank you,

Peter

Hi Peter,

The only way that I could come up with a more general solution was with
recursion, converting sets back and forth to strings. This is a sample
query on the Product dimension of Foodmart Sales (but it only partially
works):

Member [Measures].[ProductTop2Cities] AS
'iif(IsLeaf([Product].CurrentMember),
SetToStr({[Product].CurrentMember}),
SetToStr({Generate(
TOPCOUNT([Product].Children, 2, [Measures].[Unit Sales]),
StrToSet(CStr([Measures].[ProductTop2Cities]))),
Head(VisualTotals({[Product].CurrentMember,
[Product].Children - TOPCOUNT([Product].Children, 2, [Measures].[Unit
Sales])},
"* - Other")), [Product].CurrentMember}))'

SELECT {[Measures].[Unit Sales]} on columns,
StrToSet(CStr(([Measures].[ProductTop2Cities],
[Product].[All Products].[Food].[Baked Goods].[Bread]))) on rows
FROM Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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