Hello,

I am trying to write a calcuated member that does a conditional sum
based on a member property within a dimension.

I'll explain the goal of my calculation here for clarification and
background. I am calculating asset and liability amounts by company.
I have a company dimension (organized into multiple hierarchies. See
Fig 1 for a diagram.) and a dimension to indicate whether my amount is
a liability or an asset.
If a company has a netting agreement in place (determined by a member
property), my calculation needs to sum the asset and liabilty amounts
and display a single liability amount (if sum is negative) or a single
asset amount (if the sum is positive)

When I try to insert the calculation, I get this error.

"Unable to update calculated member. Formula error - cannot bind:
unknown dimension: [Counterparty].[By Netting].[Comapny Cd]"


Here is the text of the calculated member I am trying to add.

--if we're in the counterparty.netting hierarchy
IIF([COUNTERPARTY].[BY NETTING].[Company Netting
Ind].&[1].level.ordinal > 0,
--if netting and an Asset, then take sum of assets & liabilities
iif([Measures].[sum_asset_liability] > 0 AND [LIABILITY
ASSET].currentmember.name =[LIABILITY ASSET].[Ind Value].&[1].name AND
[COUNTERPARTY].[BY NETTING].[Company
CD].currentmember.properties("Company Netting Ind") = "1",
[Measures].[sum_asset_liability], 0 ),
--else, not netting, so use MTM
[Measures].[Risk Mtm])


I assume this has to do with the multiple hierarchies implemented for
this dimension. Is there anyway to explicity state which hierarchy I
want my formula to refer to?

Any help is greatly appreciated.

Thanks,
Justin

Fig 1.
Company.By Netting
-Company Netting Ind
+Netting
+No Netting
--Company Cd
+Comapny A
+Company B

Company.Standard
-Company Cd
+Company A
+Company B

The error is due to the fact that .CurrentMember applies to a
dimension/hierarchy, not to a level. From SQL BOL:

Returns the current member along a dimension during an iteration.

Syntax
«Dimension».CurrentMember
...
So [COUNTERPARTY].[BY NETTING].currentmember.properties("Company Netting
Ind") should resolve the error.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for the quick reply, Deepak.

That solved my problem.

Justin