Error on MDX Query "this way" tells me to do it "that way"....doing it "that way" error tells me to do it "this way" :(

hi gang,

  i'm finding mdx pretty frustrating far.  i've been banging head against wall day trying feel quite simple...if know doing.

  work school system, want compare retention rate of students prior year rate current year.  my dimenions/measures in play are:

[school dates].[end year].&[2010]  *prior year
[school dates].[end year].&[2011]  *current year
[school dates].[active].&[true]  *a bit field set true records in current year
[school dates].[prior year].&[true]  *a bit field set true records in prior year
[measures].[retention rate] *a percent field (13.78%, 17.52%, etc)

i've tried dozens of combinations throughout day , gotten dozens of error messages, current closest i've gotten.  if try this:

  with    member cygoal as ([school dates].[end year].&[2011], [measures].[retention rate])   member pygoal as ([school dates].[end year].&[2010], [measures].[retention rate])    select  case    when cygoal > pygoal then 1   else 0  end  on columns,    --non empty { [location].[location].[location].&[valley traditional high]}  non empty { [location].[location].[location].allmembers}  on rows    from [true freshmen - retention]  

error message: the axis0 function expects tuple set expression  argument. string or numeric expression used.

for testing purposes, used numbers in compare so:

  with    member cygoal as ([school dates].[end year].&[2011], [measures].[retention rate])   member pygoal as ([school dates].[end year].&[2010], [measures].[retention rate])    select  case    when 1 > 2 then 1   else 0  end  on columns,    --non empty { [location].[location].[location].&[valley traditional high]}  non empty { [location].[location].[location].allmembers}  on rows    from [true freshmen - retention]    

error message:  the axis0 function expects tuple set expression  argument. string or numeric expression used.

at point, keep waiting ashton kutcher walk around corner , tell me i'm being punked...please explain i'm doing wrong , how fix, if not sanity...then kids! :)

how following?

  with member [measures].[cygoal] as  ([school dates].[end year].&[2011], [measures].[retention rate]) member [measures].[pygoal] as  ([school dates].[end year].&[2010], [measures].[retention rate]) member [measures].[cy greater] as  iif(cygoal > pygoal, 1, null) select non empty {  [measures].[cy greater] } on columns , --non empty { [location].[location].[location].&[valley traditional high]} non empty {  [location].[location].[location].allmembers } on rows from [true freshmen - retention];  

note using parallelperiod function year previous current. (i.e. once know current period can use parallelperiod mdx function go or forward number of periods.) way don't need track previous year using flags etc.

---

http://bi-logger.blogspot.com/

SQL Server  >  SQL Server Analysis Services