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:

--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