CASE
CASE 1:
Description:
The function conditionally returns specific values based on multiple comparisons. There are two types of case statements:
A simple case statement that compares an expression to a set of simple expressions to return specific values.
Syntax:
CASE [input_expression] WHEN when_expression THEN when_true_result_expression [...n] [ELSE else_result_expression] END
Return Type:
SCALAR
Parameters:
Parameter | Description |
|---|---|
input_expression | It is a valid MDX expression that resolves to a scalar value. |
when_expression | A specified scalar value against which the input_expression is evaluated. If the input expression is true, then the value of the else_result_expression is returned. |
when_true_result_expression | The scalar value is returned when the WHEN clause evaluates to true. |
else_result_expression | The scalar value is returned when none of the WHEN clauses evaluate to true. |
Example:
WITH MEMBER [Measures].x AS
CASE [Measures].[Reseller Order Count]
WHEN 0 THEN 'NONE'
WHEN 1 THEN 'SMALL'
WHEN 2 THEN 'SMALL'
WHEN 3 THEN 'MEDIUM'
WHEN 4 THEN 'MEDIUM'
WHEN 5 THEN 'LARGE'
WHEN 6 THEN 'LARGE'
ELSE 'VERY LARGE'
END
SELECT {[Measures].[Reseller Order Count],[Measures].x} on 0
, NON EMPTY [Customer].[Geography].[Postal Code].Members ON 1
FROM [AdventureWorksMF]
CASE 2:
Description:
The function conditionally returns specific values based on multiple comparisons. There are two types of case statements:
A searched case statement that evaluates a set of Boolean expressions to return specific values.
Syntax:
CASE WHEN Boolean_expression THEN when_true_result_expression [...n] [ELSE else_result_expression] END
Return Type:
SCALAR
Parameters:
Parameter | Description |
|---|---|
Boolean_expression | An MDX expression that evaluates to a scalar value. |
when_true_result_expression | The scalar value is returned when the WHEN clause evaluates to true. |
else_result_expression | The scalar value is returned when none of the WHEN clauses evaluate to true. |
Example:
WITH MEMBER [Measures].x AS
CASE
WHEN [Measures].[Reseller Order Count] > 6 THEN 'VERY LARGE'
WHEN [Measures].[Reseller Order Count] > 4 THEN 'LARGE'
WHEN [Measures].[Reseller Order Count] > 2 THEN 'MEDIUM'
WHEN [Measures].[Reseller Order Count] > 0 THEN 'SMALL'
ELSE "NONE"
END
SELECT {[Measures].x} on 0,
NON EMPTY [Customer].[Geography].[Postal Code].Members on 1
FROM [AdventureWorksMF]