MicroStrategy ONE
Case Functions
Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. When MicroStrategy queries the data warehouse, the software determines which condition evaluates as true and then returns the value that the user has specified that corresponds to that condition. The case functions, their syntaxes, and examples are listed below.
Case
This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case
can be used for categorizing data based on multiple conditions. This is a single-value function.
Depending on your MicroStrategy product and licensing, this function may not be available.
Syntax
Case (
Condition1
,
ReturnValue1
,
Condition2
,
ReturnValue2
,...,
DefaultValue
)
Where:
Condition1
is the first condition to evaluate. The condition can contain metrics, comparison and logical operators, and constants.ReturnValue1
is a constant or metric value to return if theCondition1
condition isTRUE
.Condition2
is the second condition to evaluate. The condition can contain metrics, comparison and logical operators, and constants.ReturnValue2
is a constant or metric value to return if theCondition2
condition isTRUE
.- ... represents any number of Conditions and Return Values that can be passed through this function.
DefaultValue
is the information to return if none of the other conditions areTRUE
.
Example
This example generates a report where if the revenue, represented by the Total Revenue metric, is less than 300,000, the function returns a 0; if the revenue is less than 600,000, the function returns a 1; if revenue is any other value, the function returns a 2. The case metric is defined as follows:
Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2)
Difference Between Case and If
The If function is very similar to the Case function. Each function takes a condition as an argument and returns a value depending on whether the condition is true or not. The Case function can evaluate multiple conditional arguments, while the If function can only evaluate one condition. However, the If function can be evaluated by either the SQL Engine or the Analytical Engine, while the Case function is evaluated only by the Analytical Engine.
CaseV (Case Vector)
CaseV
evaluates a single metric and returns different values according to the results. It can be used to perform transformations on a metric. For example, if provided a list of values ranging from 1 to 12, CaseV
might return January for a value of 1, February for a value of 2, etc. This is a single-value function.
Depending on your MicroStrategy product and licensing, this function may not be available.
Syntax
CaseV (
Argument
,
Value1
,
Result1
,
Value2
,
Result2
, ...,
DefaultResult
)
Where:
Argument
is the metric against which all values are compared.Value1
is the first value (constant or metric) to be evaluated.Result1
is the information to return (constant or metric) if theValue1
value is equal to the value for the Metric.Value2
is the second value (constant or metric) to be evaluated.Result2
is the information to return (constant or metric) if theValue2
value is equal to the value for the Metric....
represents any number of Conditions and Return Values that can be passed through this function.DefaultResult
is the information to return (constant or metric) if none of the other values are Equal to the Metric.
Usage Notes
The metric or argument in the CaseV expression is always held as a float. This means that even if the value is 2, it is held as 2.00000; and therefore 2 and 2.00000 are never considered equal. For this reason, it is best if you wrap the metric or argument in the integer function, for example, CaseV(int(M1), 2, A,...).
Example
This simple example generates a report where if the Unit Profit for the item is 2, the function returns a 200; if the Unit Profit for the item is 3, the function returns a 300; if Unit Profit is any other value, the function returns a 1000000. Notice how a single metric, Unit Profit, is evaluated against multiple numeric values. The report contains the attribute Item and the metrics Unit Profit and CaseV. A report filter limits the items displayed to those in the subcategory Action. The CaseV metric is defined as follows:
CaseV ([Unit Profit], 2, 200, 3, 300, 1000000)
Coalesce
Returns the value of the first non-null argument. Coalesce
can be used to identify data in tables that may not be fully populated or in metric definitions.
Depending on your MicroStrategy product and licensing, this function may not be available.
Syntax
Coalesce (
Argument1
,
Argument2
,...,
ArgumentN
)
Where:
The arguments for the Coalesce
function can be any expression that can be evaluated as null or not null.
Usage Notes
You can use the Coalesce function in defining a metric, but more often it is used with the Query Builder feature to support the inclusion of the Coalesce function in SQL queries. See the example below for more detailed information.
Example
Your database has two tables T1 and T2 that include the column MONTH_ID
with the format yyyymm. You want to filter on a specific month, but you are not sure which table has been populated with month data. In the Query Builder Editor, you can filter your SQL query by creating the condition Coalesce(T1.MONTH_ID, T2.MONTH_ID) = 200410
. The WHERE clause of the SQL query checks for the first non-null MONTH_ID
column and compares it to the value 200410.
For more information on Query Builder, see the Advanced Reporting Help.