All eazyBI for Jira eazyBI for Confluence Private eazyBI

CASE statement

Lets you conditionally return specific values from 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.

  • A searched case statement that evaluates a set of Boolean expressions to return specific values.

Syntax

Simple CASE statement:

CASE [input_expression]  
WHEN when_expression_1 THEN when_true_result_expression_1  
WHEN when_expression_2 THEN when_true_result_expression_2 
[...n]  
ELSE else_result_expression --this statement is optional
END

Search CASE statement:

CASE   
WHEN Boolean_expression_1 THEN when_true_result_expression_1  
WHEN Boolean_expression_2 THEN when_true_result_expression_2 
[...n]  
ELSE else_result_expression --this statement is optional
END

Arguments

If there is no ELSE clause, and all WHEN clauses are evaluated as false, the result is an empty cell.

input_expression

A Multidimensional Expressions (MDX) expression that resolves to a scalar value.

when_expression

A specified scalar value against which the input_expression is evaluated, which, when evaluated to true, returns the scalar value of the else_result_expression.

when_true_result_expression

The scalar value is returned when the WHEN clause evaluates to true.

else_result_expression

The scalar value returned when none of the WHEN clauses evaluate to true. This argument is optional.

Boolean_expression

An MDX expression that evaluates to a scalar value.

Examples

If Sprint is closed, the boolean value will be 1, and the calculation will return "Yes", otherwise "No" will be returned:

--example of "Simple" case stetement
CASE
  [Sprint].CurrentMember.GetBoolean('Closed')
WHEN CBool(1) THEN 'Yes'
WHEN CBool(0) THEN 'No'
END


Calculates the percentage of resolved issues from issues created. In the following example, logical expression in the case statement is used for the measure "Issues created" to avoid dividing by zero, which returns infinity.

--example of "Search" case stetement
CASE WHEN
  [Measures].[Issues created] <> 0
THEN
  CoalesceEmpty([Measures].[Issues resolved],0)
  /
  [Measures].[Issues created]
END


See more about CoalesceEmpty() here


If the Due date is empty, then 60 days are added to the Issue created date; otherwise, the due date is returned. Both result_expressions should be in the same format, or the format must be manually defined:

--example of "Search" case stetement
CASE WHEN
  IsEmpty([Measures].[Issue due date])
THEN
  DateAddWorkdays(
    [Measures].[Issue created date],
    60
  )
ELSE
  DateParse([Measures].[Issue due date])
END

Read more details about DateAddWorkdays() and DateParse() functions.

See also