Case
The MDX Case function for Essbase begins a conditional expression. There are two types of conditional test you can perform using CASE: simple case expression and searched case expression.
Syntax
The simple case expression evaluates case_operand and returns a result based on its value, as specified by WHEN or ELSE clauses. The result of a case expression can be a value expression or a set. If no ELSE clause is specified, and none of the WHEN clauses is matched, an empty value/empty set is returned.
CASE
case_operand
simple_when_clause...
[ else_clause ]
END
In searched case expression, each WHEN clause specifies a search condition and a result to be returned if that search condition is satisfied. The WHEN clauses are evaluated in the order specified. The result is returned from the first WHEN clause in which the search condition evaluates to TRUE. The result can be a value expression or a set. If no ELSE clause is specified, and none of the search conditions in the WHEN clauses evaluate to TRUE, an empty value/empty set is returned.
CASE
searched_when_clause...
[ else_clause ]
END
Parameters
- case_operand
-
An expression to evaluate.
- simple_when_clause
-
One or more WHEN/THEN statements. Syntax:
WHEN when_operand THEN result
-
when_operand: A value expression.
-
result: A numeric value expression, a string value expression, or a set.
-
- else_clause
-
Optional. Syntax:
ELSE numeric_value_expression | set | string_value_expression
- searched_when_clause
-
One or more WHEN/THEN statements. Syntax:
WHEN search_condition THEN result
-
search_condition: A value expression.
-
result: A numeric value expression, a string value expression, or a set.
-
Example
Example for Simple Case Expression
In the following query, the calculated member [Measures].[ProductOunces]
is evaluated based on the value of the Ounce attribute for the current member of the Product dimension.
WITH MEMBER [Measures].[ProductOunces] AS
'Case Product.CurrentMember.Ounces
when 32 then 32
when 20 then 20
when 16 then 16
when 12 then 12
else 0
end'
SELECT
{ [Measures].[ProductOunces] } ON COLUMNS,
{ [Product].Members } ON ROWS
FROM Sample.Basic
This query returns the following result:
Table 4-43 Output Grid from MDX Example
(axis) | ProductOunces |
---|---|
Product | 0 |
Colas | 0 |
Cola | 12 |
Diet Cola | 12 |
Caffeine Free Cola | 16 |
Root Beer | 0 |
Old Fashioned | 12 |
Diet Root Beer | 16 |
Sarsaparilla | 12 |
Birch Beer | 16 |
Cream Soda | 0 |
Dark Cream | 20 |
Vanilla Cream | 20 |
Diet Cream | 12 |
Fruit Soda | 0 |
Grape | 32 |
Orange | 32 |
Strawberry | 32 |
Diet Drinks | 0 |
Diet Cola | 0 |
Diet Root Beer | 0 |
Diet Cream | 0 |
Example for Searched Case Expression
The following query divides products into different profit categories based on Profit, and returns categories for each product.
WITH MEMBER [Measures].[ProfitCategory] AS
' Case
when Profit > 10000 then 4
when Profit > 5000 then 3
when Profit > 3000 then 2
else 1
end'
SELECT
{ [Measures].[ProfitCategory] } ON COLUMNS,
{ [Product].Members } ON ROWS
FROM Sample.Basic
This query returns the following result:
Table 4-44 Output Grid from MDX Example
(axis) | ProfitCategory |
---|---|
Product | 4 |
Colas | 4 |
Cola | 4 |
Diet Cola | 3 |
Caffeine Free Cola | 1 |
Root Beer | 4 |
Old Fashioned | 3 |
Diet Root Beer | 4 |
Sarsaparilla | 2 |
Birch Beer | 2 |
Cream Soda | 4 |
Dark Cream | 4 |
Vanilla Cream | 1 |
Diet Cream | 4 |
Fruit Soda | 4 |
Grape | 4 |
Orange | 3 |
Strawberry | 1 |
Diet Drinks | 4 |
Diet Cola | 3 |
Diet Root Beer | 4 |
Diet Cream | 4 |
See Also