Given an Account dimension, where actual accounts roll up
to parents (e.g. 700-01-01, 700-01-02, etc. rollup to
Revenue; 500-01-01, 500-02-02, etc. rollup to Expenses,
etc.) and a Measures dimension that stores the type of
data (i.e. dollars and volumes). We need to calculate
royalty rates (my royalty account dollars / total royalty
dollars). Since all royalty accounts will be descendants
of the Royalties parent, it would be nice to write a
formula like:

If I'm a descendant of Royalties, then take my dollars and
divide it by the total Royalties dollars.

The IsAncestor function is pretty useless at this (as far
as I can tell) in that I'd have to write an IIF statement
for each level zero account that falls under Royalties.
Is there a function that can determine if the current
member is a descentant of another member instead? This
would make it much simpler (one IIF statement) to
calculate this value.

Thanks in advance.

Not sure why IsAncestor() won't work, since this is a two-way
relationship, ie. if IsAncestor(A, B), then B is also a descendant of A.
Here's an example from Foodmart:

'iif(IsAncestor([Product].[All Products].[Drink],
[Product].CurrentMember),
[Measures].[Store Sales]/([Measures].[Store Sales],
[Product].[All Products].[Drink]), NULL)',
FORMAT_STRING = 'Percent'

Select { [Measures].[DrinkRate]} on columns,
Non Empty [Product].[Product Name].Members on rows
From Sales
Where ([Store Type].[All Store Type].[Small Grocery])

- Deepak

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