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 evaluate to 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 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 boolan value will be 1 and calculation will return "yes", otherwise "No"

--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 Due date is empty then 60 days are added to Issue created date, otherwise the due date is returned. Both result_expressions should be in the same format or 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

See more about DateAddWorkdays() and DateParse() here

See also