So, is there any value to allowing a custom member property in a parent
child dimension???

Books Online says that you can add custom member properties to a "specific
named level" in a dimension. Due to the manner in which a parent child
dimension recursively creates levels, I find that no MDX expression can
reference a custom member property, whether I use a construct like
[dim name].CurrentMember.Properties("property name") or
[dim name].[Level 03].Properties("property name") or even
[dim name].[level name].Properties("property name") where level name comes
from a level naming template.

However, if I use an instrinsic member property, like [dim
name].CurrentMember.Properties("KEY") it works just fine - probably because
this can be resolved at any level.

My main intent was to use the custom member property in a filter expression,
to select an element from the dimension based upon the properties value
instead of the member name or key field. About the only thing I haven't
tried yet is creating a virtual dimension from the member property and use
that instead.

Is there any way to reference a non-intrinsic member property in a
parent-child dimension?

There certainly is - though it can get a bit tricky, as parent-child
dims have a single level of a special type:

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agdimensions_5ab7.asp
Unlike regular and virtual dimensions, which are defined with a number
of levels that determines the number of levels seen by end users, a
parent-child dimension is defined with a single level of a special type
that usually produces multiple levels seen by end users. The number of
displayed levels depends on the contents of the columns that store the
member keys and the parent keys. This number can change when the
dimension table is updated and the cubes using the dimension are
subsequently processed.
...

An example is the [Employees] dimension in Foodmart, which has various
Member Properties, from which the [Position] virtual dimension is
derived. Here's a Foodmart query:

'Employees.CurrentMember.Properties("Management Role")'
select
{[Measures].[Role]} on columns,
Descendants(Employees,,Leaves) on rows
from HR

- Deepak

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

Deepak, thanks for the response. I'd already tried variations of this, but
it was still not working. In the end, it turned out to be a problem with
the comparison I used in the filter, and I've got a workaround but I'm
curious what I'm doing wrong. I know I'm missing something simple, but I
just can't see it.

To continue with the foodmart example, consider the following two queries.
In the MDX Sample Application, the first doesn't work (produces a
compilation error), but the second one does.

Any ideas?
-- Use of Member Properties in a Parent Child Dimension - fails with message
Formula Error - Syntax Error - Token is not valid
with member [Measures].[Role] as
'Employees.CurrentMember.Properties("Management Role")'
select
{[Measures].[Role]} on columns,
Filter(Descendants(Employees,,Leaves), [Measures].[Role] = "Store Temp" ) on
rows
from HR

-- Use of Member Properties to filter a Parent Child Dimension that Really
Works!
with member [Measures].[Role] as
'Employees.CurrentMember.Properties("Management Role")'
select
{[Measures].[Role]} on columns,
Filter(Descendants(Employees,,Leaves),INSTR( 1, [Measures].[Role], "Store
Temp" ) >0 ) on rows
from HR


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Mark,

Text measures have some quirks in MDX expressions: for example, iif
alternate clause strings have to be "hidden". Don't know if/where [MS]
has documented it, but this works:

'Employees.CurrentMember.Properties("Management Role")'
select
{[Measures].[Role]} on columns,
Filter(Descendants(Employees,,Leaves),
CStr([Measures].[Role]) = "Store Temp Staff") on rows
from HR

- Deepak

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

Thanks Deepak, that does the trick! I figured that I was missing some sort
of "Mother, May I?" syntax...

Best regards, Mark
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote