IIF

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 )

search_condition An expression to evaluate as true or false.
true_part A value_expression or a set. IIF returns this expression if the search condition evaluates to TRUE (something other than zero).
false_part A value_expression or a set. IIF returns this expression if the search condition evaluates to FALSE (zero).

Example

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:

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

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com