Hi,
I have a problem with peforming a topcount on a dimention with following

Level 1 Level 2 Level 3 Level 4 Level 5
A A A A B
A A A B B
A A A B C
X C A E B
X A D A B

What I want to do is take any level and perform a topcount of the lowest
level for example if I was at LEVEL 3 and "A" was selected. I should get a
count of B=3 , C=1 however becouse level 5 "B" is under difrent parents I
get "B" repeated.

Can anyone help with is one I have tried everything.


I have included the MDX code that I am using,
SELECT

Non Empty

{ [Measures].[Total Visits] } ON COLUMNS,

Non Empty

{ TopCount ( { Generate ( {
[dimReferrerSearch].[MediaGroup].&[G-1].&[G-2] },

{ Order ( { Descendants ( [dimReferrerSearch].CurrentMember,

[dimReferrerSearch].[SearchPhrase],

SELF ) },

[dimReferrerSearch].CurrentMember,

BDESC ) } ) },

50,

[Measures].[Total Visits] ) } ON ROWS

FROM

[cubVisit]

WHERE

[dimCampaignID].[All dimCampaignID].[200047]

Here is a Foodmart Sales cube query that categorizes all descendants of
a product at the Brand Name level. Note that, although a Brand Name may
appear multiple times in the hierarchy, each is listed only once in the
results. This seems similar to what you are trying to do:

'CreatePropertySet([Store],
Descendants([Product].CurrentMember,[Product].[Brand
Name]),
[Product].CurrentMember.Name)'
Select {[Unit Sales]} ON COLUMNS,
TopCount(BrandNames,10,[Measures].[Unit Sales]) ON ROWS
From Sales
Where [Product].[All Products].[Drink]
The "CreatePropertySet()" MDX function is not documented in BOL, but
here is a SQL Server mag article by Russ Whitney.
In it, limitations on the maximum set size are discussed:

http://www.sqlmag.com/Articles/Index...rticleID=16302



- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!