Dimension formulas crossing alternate hierarchies in 7.5 with SQL2008

[Former Member]

I am trying to generate a new calculated member in the Entity dimension and am not sure of the syntax when it crosses hierarchies.  The calculation also involves a parent member.  If I have

Test_H1 = [ENTITY].[CONS] where CONS is in H1 then it works fine. 

If I do

Test_H2 = [ENTITY].[CONS_H2] where CONS_H2 is a parent in H2 but TEST_H2 does not have a parent designated so is defaulting to H1; this will not work.  I get no data returned.  I have tried Test_H2 = [ENTITY].[H2].[CONS_H2] but this also does not work. 

I am not sure if this is a syntax problem or a failure of the product.  Does anyone know the correct syntax for a SQL2008 environment when it is crossing hierarchies.  I can not find documentation anywhere.

[former_member186498]

Hi Karen,

can you please show us your entinty dimension?

Regards

     Roberto

[Former Member]

I

[JohnL]

Hi Karen,

A screen shot or more descriptive interpretation of what you are trying to achieve will help.

I can't recall if the MDX language supports cross formula syntax  but i'm not too sure exactly what you are trying to do.

You can start by looking at this and MDX Function Reference (MDX)

Also a simple example in ApShell or environmentShell might be better to try out and will allow others on this forum to test and/or help you more.

Have you also tried asking your consulting partner?

Thanks,

John