HI All,

I am using a calculated measure that includes a Dimension Member
Property. The formula result is perfect for all dimenion members
except the All Memeber level. The All Member Level returns #ERR.

The formula is:

iif((StrToValue([Account].CurrentMember.Properties("Account_No")) >=
7000), [Measures]. [ClosingBalance],
[Measures].[YtdActual])

In english it does the following
- convert the member property value Account_No to a numeric value
- Test to see if the value is greater than or equal to 7000
- If true then use the Closing Balance Measure
- If False then use the Year to Date Actual Measure

Any help greatly appreciated.
Cheers
Ryan

Ryan Castle wrote:

The All level is a special level that doesn't correspond to data in the
dimension table, therefore the Dimension Property Account_No doesn't
exist for the all level. What you could do to get it right is:

IIF( [Account].CurrentMember.Level is [Account].[All Account],
//The special all level handling, aggregate the property here
IIF( Max( [Account].CurrentMember.Children,
StrToValue( [Account].CurrentMember.Properties ("Account_No") )
) >= 7000,
[Measures].[closingBalance],
[Measures].[YtdActual ),
//The regular level handling, copying your formula...

The problem is that in general an account number doesn't aggregate so
easily, but maybe in your case using Min or Max makes sense?

You could add

[Account].CurrentMember.Level.ordinal > 0

to the IIF. The result for the All level will then be empty.

Regards,
Tom


ryan.castle (AT) eone (DOT) co.nz (Ryan Castle) wrote in message news:<13894fcf.0406212226.6a6b841b (AT) posting (DOT) google.com>...