If Clause

The if clause, iif, can be used to test any search condition and choose one value or another based on whether the test is true or false. The BMF construct is:

<if_expression> ::= iif(<search_condition>, <true_part>, <false_part>)
<true_part> ::= <value_expression>
<false_part> ::= <value_expression>

The data type of true_part must be the same as the data type of false_part. That is, either both are numeric value expressions or both are string value expressions.

The following example demonstrates the use of iif. The query displays actual sales, budgeted sales, and variance (that is, the difference between actual and budgeted sales) for Asia and Europe for each quarter of 1991, for all sales reps and all products. The conditional expression is used to keep the variance always positive.

WITH MEMBER [Measures].[Variance] AS iif ((Sales > BudgetedSales), 
  (Sales - BudgetedSales), (BudgetedSales - Sales))
SELECT
  {Sales, BudgetedSales, Variance} ON COLUMNS,
  CROSSJOIN({Asia, Europe}, {Qtr1, Qtr2, Qtr3, Qtr4}) ON ROWS
FROM SalesCube
WHERE ([1991], [SalesPerson].[ALL], [Products].[ALL])