Calculation in MDX Query

• smthembu

  Hall of Fame

  Points: 3464

  More actions

  June 25, 2010 at 2:12 am

  #222547

  Hi All

  I have the below query it works fine in the dataset but now i need to calculate the results, e.g. completed\enrolled*100 i should get 6.9% but i'm not good with MDX. How do i go by. Help Tx!!

  SELECT NON EMPTY { [Measures].[Fact Count] }

  ON COLUMNS, NON EMPTY { ([Dim Attendance Status].[Attendance Status].[Attendance Status].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

  FROM ( SELECT ( { [Dim Attendance Status].[Attendance Status].&[Completed], [Dim Attendance Status].[Attendance Status].&[Enrolled] } )

  ON COLUMNS FROM [Learning]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  It's better to fail while trying, rather than fail without trying!!!

• Abs-225476

  Hall of Fame

  Points: 3483

  More actions

  June 28, 2010 at 9:09 am

  #1185842

  Hi,

  Your calculation for the completed field will be something like

  =sum(completed_field)

  and for the % calculation it will be

  =sum(completed_field)/sum(enrolled_field)

  if this returns a divide by zero error the formula will need and 'if' in there

  ==iif(sum(enrolled_field)=0,0,sum(completed_field)/sum(enrolled_field) )

  If there are any errors in your expression a red squiggly underline will show in the expression window.