I have a question about Calculated Members. My MDX knowledge is fairly
basic.

The setup: I have a measure in my cube that is a count of the number of
people records in a table. I have a dimension with attributes such as
age, gender, race, etc. I've got several calculated members defined
that slice the people count by some of these attributes - for instance,
the number of female persons, defined as ([Measures].[Person Count],
[Characteristics].[Gender].&[Female]). These work great.

What I'm trying to do now is create two new calculated members: one for
a count of persons under age 35, another for a count of persons over
35. And I'm finding it's a bit tricky. I've gotten it to work, but it's
hack-y, and I'd like to find a more elegant solution.

What I have now is two members that basically look like this:

([Measures].[Person Count], [Characteristics].[Age].&[1]) +
([Measures].[Person Count], [Characteristics].[Age].&[2]) +
etc... up to ([Measures].[Person Count], [Characteristics].[Age].&[35])

and another similar looking one for over 35. The biggest problem with
this is when I hit on an age that isn't defined as a member because it
doesn't occur in the data. For instance, if I have no one in my data
that is 10 years old, when I include ([Measures].[Person Count],
[Characteristics].[Age].&[10]) in the calculated member definition, the
cube won't process because that member doesn't exist. I've worked
around this for now by just not including ages that don't exist, but
that won't work very well when my data changes.

Is there a way to test to see if a member exists, so that I can add it
in if it exists or skip it if it doesn't? For instance something like:

IIF(MemberExists([Characteristics].[Age].&[1]), ([Measures].[Person
Count], [Characteristics].[Age].&[1]), 0) - meaning that if the member
exists, add it's count value, if not then add zero? Or is there an even
more elegant way to solve this problem?

Thanks for any help you can provide!

M

You can use the VBA IsError() function, as in this Foodmart Sales
sample. AS 2005 has a "Missing Members Mode" that could be used to
ignore such errors:

'iif(IsError(StrToValue("[Store Size in SQFT].[All Store Size in
SQFT].[20318]")),
"Missing", "Present")'

select {[Measures].[TestExist]} on columns
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

If your ranges are relatively fixed, another option would be to add
another level to your characteristics dimension. For the members without
groupings you would setup the "HideMemberIf" property to hide the extra
level.

eg. If you were to set up your characteristics dimension with
"HideMemberIf" set to "Same Name as Parent" you could set up your
dimension as follows:

Characteristic, Group, Detail

Age, 1-35, 1
Age, 1-35, 2
....
Age, 35 +, 35
Age, 35 +, 36
Age, 35 +, 37
....
Gender, F, F
Gender, M, M


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1134603617.997978.89020 (AT) o13g2000cwo (DOT) googlegroups.com>,
mmishkoff (AT) yahoo (DOT) com says...