Need Help---MDX Query

with
 
member     [interviewtype].[membername].[onboard] '(  [status].[membername].&[onboard],

 [interviewtype].[membername].[all]

)'
 
set test2 '{  [interviewtype].[membername].[onboard]  }'

 

set level1 '{  [interviewtype].[membername].&[hr screening],
                        [interviewtype].[membername].&[skills screening],
                        [interviewtype].[membername].&[skills interview],
                        [interviewtype].[membername].&[final interview],
                       [interviewtype].[membername].&[hr interview],test2  }'

im using this(above) query combine 2 different dimensions 1 named set.

member [measures].x  

    case   [interviewtype].[membername]
                   when  'onboard' then    ([measures].[onboardss%])
    else   
            ([measures].[pass through rates])

     end

select  { measures.[x] } on 0
,
{ level1} on 1
from [pipeline_report]
where ([time].[year].&[2008])

gives result:

                               x
hr screening         0.349623273336124
skills screening      0.267749911691982
skills interview        0.106102911846829
final interview         0.91544885177453
hr interview            0.723970944309927
offer accepted        1
onboard                 1

else part doing fine..but..it not giving in proper format though have used format_strring in measure 'pass through rates'.

 

measure'on boardss%' not giving correct values.

 

is mdx query  correct?  please correct me if im wrong...

hope clear..pls let me knw if info reqd.
 
please help!!!

regards,

 

ok,

 

we'll solve this.

 

there few improvement need done in definition of x, , these are:

- don't use type of case because evaluates values (a = b), use 1 "a b"

- ment ".name" in end, compare name (value) string ('onboard')

- better though compare members using "is"

- provide format string x also

 

here's core part:

 

code snippet

member [measures].x as

case

when [interviewtype].[membername].currentmember

is

[interviewtype].[membername].[onboard]

then [measures].[onboardss%]
else [measures].[pass through rates]

end,

format_string = "percent"

 

 

regards,

 

 

tomislav piasevoli

business intelligence specialist

http://www.softpro.hr

 

SQL Server  >  SQL Server Analysis Services