Error with calculated member in dimension with multiple hierarchies

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

.

Relevant Pages

  • Re: sum a measure on several dimensions
    ... Would I be correct in assuming that you want the sum at the intersection ... If you have a set of member in each dimension then you will need to ... > I tried the following MDX formulas on one dimension each time and they ...
    (microsoft.public.sqlserver.olap)
  • Re: Setting the default member
    ... In the dimension editor I canīt use calc members!!! ... Also there are no MDX formulars like SUM are allowed. ... in the MDX of Default Member in the dimension editor ...
    (microsoft.public.sqlserver.olap)
  • Re: Setting the default member
    ... In the dimension editor I canīt use calc members!!! ... Also there are no MDX formulars like SUM are allowed. ... in the MDX of Default Member in the dimension editor ...
    (microsoft.public.sqlserver.olap)
  • Re: Sum of selected members
    ... Member A: 10 ... Sum of selected members: 25. ... So the number 25 is what I need to get from my calculated member. ... > the underlying relational dimension table and then using this grouping ...
    (microsoft.public.sqlserver.olap)
  • Re: Slow member loading in excel 2003 pivot table
    ... it loads member quickly... ... I'm gonna add a transform step in my cube loading and just truncate the data... ... Maybe a simply filter another file extension dimension table with a ">3)" and set all the fact with the missing key error to a default member... ... I'm gonna sound crazy, bu I need to find a way to create various sized dimension and see how excel react and display them, I want to range from ...
    (microsoft.public.sqlserver.olap)