IIF

The MDX IIF function for Essbase performs a conditional test, and returns an appropriate numeric expression or set depending on whether the test evaluates to true or false.

Syntax

IIF ( search_condition, true_part, false_part )

Parameters

search_condition

An expression to evaluate as true or false (see MDX Grammar Rules).

true_part

A value_expression or a set. IIF returns this expression if the search condition evaluates to TRUE (something other than zero).

The value_expression can be a numeric value expression or a string value expression.

false_part

A value_expression or a set. IIF returns this expression if the search condition evaluates to FALSE (zero).

The value_expression can be a numeric value expression or a string value expression.

Example

Example 1

The company plans an expensive promotion of its caffeinated drinks. For the Caffeinated products only, the following query calculates a Revised Budget that is 110% of the regular budget.

WITH MEMBER 
 [Scenario].[Revised Budget] 
AS
 'IIF (
      [Product].CurrentMember.Caffeinated,
      Budget * 1.1, Budget
 )'
SELECT
 {[Scenario].[Budget], [Scenario].[Revised Budget]}
ON COLUMNS,
 [Product].Levels(0).Members
ON ROWS
FROM Sample.Basic
WHERE ([Measures].[Sales], [Year].[Qtr3])

This query returns the grid:

Table 4-81 Output Grid from MDX Example

(axis)	Budget	Revised Budget
100-10	18650	20515
100-20	8910	9801
100-30	3370	3370
200-10	11060	12166
200-20	9680	10648
200-30	3880	3880
200-40	2660	2660
300-10	10600	11660
300-20	3760	4136
300-30	8280	9108
400-10	7750	7750
400-20	6800	6800
400-30	3290	3290
100-20	8910	8910
200-20	9680	9680
300-30	8280	8280

Example 2

The following query calculates a Revised Budget equaling Budget for caffeinated products, and Actual for non-caffeinated products.

WITH MEMBER 
 [Scenario].[Revised Budget] 
AS
 'StrToMbr(IIF (
      [Product].CurrentMember.Caffeinated,
      "Budget" , "Actual"
 ))'
SELECT
 {[Scenario].[Budget], [Scenario].[Revised Budget]}
ON COLUMNS,
Children([100])
ON ROWS
FROM Sample.Basic
WHERE ([Measures].[Sales], [Year].[Qtr3])

This query returns the grid:

Table 4-82 Output Grid from MDX Example

(axis)	Budget	Revised Budget
Cola	18650	18650
Diet Cola	8910	8910
Caffeine Free Cola	3370	3189