Search code examples
sqlssasmdxdata-warehouse

Selecting a set based on an IF OR statement

IF [Sales].[Retail] > 0 OR [Sales].[Reseller] > 0 
      SELECT [Measures].[Revenue] on 0 FROM [Cube]
ELSE
      Return nothing

The above is a pseudo code representation for the type of behavior that I want. Is there a way to conditionally select in MDX based on an IF X OR Y boolean expression? I've looked at an if/else statement and case statements, but all the examples I have seen have used constants and I have struggled with mixing these functions and select statements (from the MSDN article, I am not sure it is possible).

Solution

  • How about :

    SELECT
  IIF( [Sales].[Retail] > 0 OR [Sales].[Reseller] > 0 , { [Measures].[Revenue] } , {} ) on 0 
  FROM [Cube]