Export (0) Print
Expand All

IIf Function

SQL Server 2000

IIf Function

The IIf function in Multidimensional Expressions (MDX) can be used to perform simple, yes-or-no decisions. For example, consider the following MDX query example.

WITH MEMBER [Measures].[BigSeller] AS 
   'IIf(Measures.[Store Sales] > 20000, "Yes", "No")'
   {[Store].[Store Name].Members} ON COLUMNS,
   {[Measures].[Store Sales], [Measures].[BigSeller]} ON ROWS
FROM Sales

The MDX query example returns two rows for each store in the Sales cube. One row, the [Measures].[Store Sales] member, supplies the total store sales for each store. The second row is a calculated member that, based on the store sales for each store, determines if the store is a "big seller". That is, the IIf function is used to check a simple yes-or-no condition. In this case, the condition is whether or not the store sales figure for each store is greater than $20,000.00. If it is, the value of the member for that store is Yes. If the store sales figure is equal to or less than $20,000.00, it returns the value No.

This is a simple but graphic example of the use of the IIf function to return different values based upon a single Boolean condition; other MDX functions and operators can be used to supply the returned values in the IIf function.

For more information about the syntax of the IIf function, see IIf.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft