MDX where with AND operator

• lawson85

  SSC Veteran

  Points: 202

  More actions

  January 29, 2015 at 3:25 am

  #314834

  Hi All,

  I have a MDX query that requires the values of three columns read the rowset not included in the results if the column values are 1. I have the following mdx query

  SELECT NON EMPTY { [Measures].[Actual Value] } ON COLUMNS, NON EMPTY { ([Metric].[Hierarchy].[Department].ALLMEMBERS * [Metric].[App Flag1].[App Flag1].ALLMEMBERS * [Metric].[Appr Flag2].[Appr Flag2].ALLMEMBERS * [Metric].[Approver1].[Approver1].ALLMEMBERS * [Metric].[Approver2].[Approver2].ALLMEMBERS * [Metric].[Capt Flag].[Capt Flag].ALLMEMBERS * [Metric].[Capturer].[Capturer].ALLMEMBERS * [Metric].[Measure Chief Director].[Measure Chief Director].ALLMEMBERS * [Date].[Calendar Hierarchy].[Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Scorecard] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  where EXCEPT({[Metric].[App Flag1].ALLMEMBERS &[1]} AND {[Metric].[App Flag2].ALLMEMBERS &[1]} AND {[Metric].[Capt Flag].[Capt Flag].ALLMEMBERS &[1]})

  How can I return all records except for where these columns have the value 1.

  [Metric].[App Flag1].[App Flag1].ALLMEMBERS [AND

  [Metric].[Appr Flag2].[Appr Flag2].ALLMEMBERS AND

  [Metric].[Capt Flag].[Capt Flag].ALLMEMBERS

  Any help with formating this query is welcomed.