The case..when..then.. instruction in MDX ?

[TheHungarian]

Hi,

I just cannot find the correct syntax... The idea is to replace several iif() by something like that :

with 
member measures.test as 
'case commercial.currentmember.level.ordinal
   when 1 then measures.quantity
   when 2 then measures.sales
   else measures.budget
end'
select 
{measures.test} on 0,
{descendants(commercial.[All commercials],1,self_and_after)} on 1
from Cube1
where (time.[2005])

Any idea ?

 

[MDXer]

I would have to check my mdx books but I don't believe the CASE statement is valid in MDX.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[justins57]

Hungry,

MDXer is correct. There is no Case Statement in AS 2000, there will be in AS 2005. The only way to do this is with the IIF statement - IIF(test, true expression, false expression).

Justin

 

[MDXer]

exactly what are you attempting to do maybe the folks here might have some alternate ideas to get you what your after.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[TheHungarian]

My problem :
I must return only one measure (measures.a) in my columns set and the formula of this calculated measure depends on the current member of the rows set (actually, the current member of {set1}) :

select 
{measures.a} on 0,
{crossjoin({set1},{set2})} on 1
from MyCube

The easiest way was to declare measures.a like this :

with
member measures.a as 'StrToTuple(
case set1.currentmember.name
when "A" then "(time.currentmember,measures.sales)"
when "B" then "(time.currentmember,measures.budget)"
[...]
else "(time.currentmember.lag(12),measures.budget)"
)'

But since the CASE instruction does not exist I found a tricky way to replace it :

with 
member measures.A as '(time.currentmember,measures.sales)'
member measures.B as '(time.currentmember.measures.budget)'
[...]
member measures.F as '(time.currentmember.lag(12),measures.budget)'
member measures.a as 'StrToMember("measures."+set1.currentmember.name+")'

By the way, this method is far better than multiple IIF because I could not have done a sum(lastperiods({}),) with multiple IIF.

Could be some kind of tip, don't you think ?