CODEWITHSUNDEEP
sqlssasmdxdata-warehouse
stevebot
stevebot

Reputation: 24005

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).

Upvotes: 0

Views: 274

Answers (2)

Vikram Jain
Vikram Jain

Reputation: 5588

select (case when ([Sales].[Retail] > 0 OR [Sales].[Reseller] > 0) 
              then  [Measures].[Revenue] else 'nothing' end) as 'Revenue' from [Cube]

Upvotes: 1

Marc Polizzi
Marc Polizzi

Reputation: 9375

How about :

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

Upvotes: 1

Related Questions