I am new to MDX. Could anyone please help me ?



I have defined a dimension LOB which has 3 summary levels - Level1,

Level2, Level3.



I have a fact table with measures and this dimension key joined to the
fact table.



I am trying to total up the measure "Loss" after excluding a set

of values within the LOB dimension. These values can be from any level

of the Lob dimension



The dimension LOB looks like this:



Level1 Level2 Level3

------ ----- ------

BM BM BM

Bonds Bonds Fidelity

Bonds Bonds Surety

CAuto CAuto BI CAuto BI X UMBI

CAuto CAuto BI CAuto UMBI

CAuto CAuto PD CAuto PD

CAuto CAuto PIP CAuto PIP

CAPD CAPD CAPD



If the totals for all LOBs is say X

If the total for CAuto BI alone is Y

If the total for CAPD alone is Z

Then the total for All Excluding CAuto BI and CAPD should return

X-(Y+Z)



I would like to exclude multiple values for this dimension and get the

correct totals.



I used the following mdx, but it returns wrong results :



with member [LOB].[Excl] as 'Sum(Except([LOB].[All

LOB].Children),{Descendants([LOB].[All LOB].[CAuto Liab].[CAuto

BI],1,Self_Before_After),Descendants([LOB].[All
LOB].[CAPD],1,SELF_BEFORE_AFTER)}))'

select {[LOB].[Excl]} on rows,

{[Measures].[Loss]} on columns

from RCube





Thank you


--
Posted via http://dbforums.com