I have a problem

My Dimension looks like this

[Product]
-- [Brand Family]
---- [Sub Brand Family]
------ [Retail SKU]

I need a query that will generate the Top 1 [Retail SKU] by [Brand
Family] Based on [Measures].[SOM]

Does anyone know how this could be done.

I have done a search but everything I get seems to tell me how to do
this kind of calculation based on different dimensions where as with
mine they are all in the same dimension

Thanks in advance

Denver

I think it may be something to do with DrillDownMember or
DrillDownLevel but can't seem to quite get it!!

Any help?

hi,

Edit the MDX in Business Intelligence Development Studio and and in the
filter pane select the hierarchy and in the operations select top N.

Kind regards,
-------------------------------------
http://www.Designing-Systems.com
Email. Support (AT) Designing-Systems (DOT) com
------------------------------------------------

"Denver" wrote:

Here's an Adventure Works example, which returns the top-selling Product
under each of 4 Product Categories:

Generate([Product].[Product Categories].[Category].Members,
TopCount(Existing [Product].[Product Categories].[Product Name].Members,
1, [Measures].[Sales Amount])) on 1
from [Adventure Works]
-----------------------------------------------------------
Sales Amount
Hitch Rack - 4-Bike $237,096.16
Mountain-200 Black, 38 $2,589,363.78
Classic Vest, S $156,398.07
HL Mountain Frame - Silver, 38 $412,969.20

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

This isn't working on my CUbe

The Query

SELECT {[Measures].[SOM]} on 0,
GENERATE([Product].[Brand Family].Members, TOPCOUNT(EXISTING
[Product].[Retail SKU].Members, 1, [Measures].[SOM])) ON 1
FROM [Channel Standard]

Give me a "Invalid Token" error ^[Product].[Retail SKU]^

It works when I remove the "existing" part but then just gives me the
top 1 [Retail SKU] in the whole dataset

Any ideas why it's doing this?

Thanks

Denver

Are you using AS 2000 - "existing" won't work there?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Yes

So what do I need ot do now?

If I do this

WITH
MEMBER [Measures].[Level] AS '[Product].CurrentMember.Level.Ordinal'
SET [Sub Brand] AS 'DRILLDOWNLEVELTOP({[Product].[Brand
Family].Members}, 1, [Product].[Brand Family], [Measures].[Volume])'
SELECT {[Measures].[Volume], [Measures].[Level]} ON 0,

NONEMPTYCROSSJOIN(DRILLDOWNLEVELTOP({[Sub Brand]}, 1, [Product].[Sub
Brand Family], [Measures].[SOM])) ON 1
FROM [Cube]

Basically it gets the Biggest [Sub brand Family] within the [Brand
Family] then it will get the biggest [Retail SKU] within that [Sub
Brand Family] which is almost there but what if my biggest [Retail SKU]
per [Brand Family] isn't in the largest [Sub Brand Family] this will
yield incorrect results.

I need the biggest [Retail SKU] (Level 3) per Brand Family] (Level 1)

I hope this "half solution" can help you to help me

Thanks again

Denver

Going back to the earlier query (with the error), what if you use
Descendants() instead of Existing, like:

GENERATE([Product].[Brand Family].Members,
TOPCOUNT(DESCENDANTS([Product].CurrentMember,
[Product].[Retail SKU]),
1, [Measures].[SOM])) ON 1
FROM [Channel Standard]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Spot on my Friend

Thank you very much

Denver