A logical script in MDX to perform an “If..Then.. Else..” block.
IIf(«Logical Expression», «Numeric/String Expression1», «Numeric/String Expression2»)
Example1:The following example returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise:
IIf(IsEmpty(Measures.CurrentMember), 0, 1)
Example2:The following string returns the string “Yes” if Measures.CurrentMember is an empty cell, the string, “No” otherwise:
IIf(IsEmpty(Measures.CurrentMember), "Yes", "No")
Example3:
IIf(
[Scenario].CurrentMember IS [Scenario].[Budget],
[Measures].[Budget Unit Sold],
[Measures].[ActualUnit Sold]
)
Example4:Average Sales (Measure) By Time Day (Dimension)
IIf (
([Time].currentmember.level.name = "(All)" OR
[Time].currentmember.level.name = "Year" OR
[Time].currentmember.level.name = "Quarter" OR
[Time].currentmember.level.name = "Month" OR
[Time].currentmember.level.name = "Day"
),AVG(Descendants(Time.CurrentMember,[Time].[Day]),Measures.[Sales]),0)
Source:
SQL Server 2000 Book Online (F1)
MDX Solution With Microsoft SQL Server Analysis Service (Wiley) (George Spofford)