Not trivial calculated member in MSAS

hello all,

i need create complicated calculated member similar sql statement “select in”. there 2 dimensions errors , products. each product assigned error or has no error. each product has amount. example, have errors e1, e2, e3 , n/a no errors , have 3 products p1, p2 , p3. instance have following situation:

product error amount
p1        e1    10
p2        e1    20
p3        e2    30
p1        e2    10
p1        e3    10
p2        e3    20
p2        n/a  20
p2        n/a  20
p1        n/a  10

and need see following in cube

error amount failed amount total
e1 30 120
e2 40 70
e3 30 120
n/a 50 120

amount failed amount each error. e1 these products p1 + p2 -> 10 + 20 = 30. e2 -> p3 + p1 -> 30 + 10 = 40. e3 -> p1 + p2 -> 10 + 20 = 30. n/a -> p2 + p2 + p1 -> 20 + 20 +10 = 50. simply- problem amount total. amount total sum of amounts of products exist on current level. example e1 following products assigned: p1 , p2. need find amounts p1 , p2 on levels. these 10 (p1 e1) + 20 (p2 e1) + 10 (p1 e2) + 10 (p1 e3) + 20 (p2 e3) + 20 (p2 n/a) + 20 (p2 n/a) + 10 (p1 n/a) = 120. , problem! if write sql this: select sum(amount) cube product in (select product cube error = ‘e1’) , on e2 , e3. how can same mdx?

any appreciating.
thanks , best regards,
andrej

hi andrej,

assuming 2000 syntax, maybe this:

sum(nonemptycrossjoin([product].[product].members,

{[error].currentmember}, {[measures].[amount]}, 1),

([measures].[amount], [error].[all errors]))

SQL Server  >  SQL Server Analysis Services