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 the Condition1 condition is TRUE.
  • 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 the Condition2 condition is TRUE.
  • ... 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 are TRUE.

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 the Value1 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 the Value2 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.