The CASE statement is similar to the IF statement. However, it allows you to apply more complicated logic than a simple yes or no response. It can be written as a series of nested IF Statements, but this can become difficult to understand if you have a lot of variables in your calculation.

The structure of a CASE Statement is as follows:

CASE
WHEN Statement1 THEN Outcome1
WHEN Statement2 THEN Outcome2
…
WHEN Statementx THEN Outcomex
ELSE Alternative
END

Within the CASE Statement you have two essential pieces of syntax at the start and end, being CASE and END respectively, every CASE statement requires these in order to work and you will not be able to create a CASE statement without them.

Examples of CASE Statements

Example 1

When an Organisation has an industry of ‘Manufacturing’ output ‘Team 1’ if it is ‘Education’ output ‘Team 2’ otherwise output ‘Team 3’

The CASE Statement can then be broken down as following:

Statement1:  industry = ‘Manufacturing’
Output1: ‘Team 1’

Statement2: industry = ‘Education’
Output2: ‘Team 2’

Alternative ‘Team 3’

Putting this together you will get:

CASE
WHEN industry = ‘manufacturing’ THEN ‘Team 1’
WHEN industry = ‘education’ THEN ‘Team 2’
ELSE ‘Team 3’
END

Example 2

When a Case Record is a high priority and in status new, call it ‘Very High Priority’, if it is medium priority and status new, call it ‘Medium Priority’ if it is high Priority case but the status is not new, call it ‘High Priority’ - If none of these criteria are met, then it is  ‘Low Priority’.

We will then need to break this up into each statement and outcome:

Statement1: Case priority is high and status is new
Output1: Very High Priority

Statement2: Case priority is medium and status is new
Output2: Medium Priority

Statement3: Case priority is high and status is not new
Output3: High Priority

Alternative: Low Priority

This then becomes the following within the CASE statement:

CASE
WHEN priority_name = '(2) High' AND  status_name = 'New' THEN 'Very High Priority'
WHEN priority_name = '(3) Medium' AND  status_name = 'New' THEN 'Medium Priority'
WHEN priority_name = '(2) High' AND  status_name != 'New' THEN 'High Priority'
ELSE 'Low Priority'
END

Example 3

Case statements are also a great way to apply CSS styling to columns as you are able to apply a variety of colours to cells, depending on a number of criteria. Using the example above, we will again start writing out what we want to achieve in plain english, but changing the outputs from “priorities” to CCS styling colours this time:

When a Case Record is a high priority and in status new, have a red background with bold black text, if it is medium priority and status new, have a yellow background with black text, if it is high Priority case but the status is not new, have a red background with black text  otherwise it is a green background with black text.

This CASE statement has the same logic as Example 2 however this time we are applying CSS styling rather than outputting values. More information on CSS styling you can be found in our Knowledge Base.

The CASE statement will then look like the following:

CASE
WHEN priority_name = '(2) High' AND  status_name = 'New' THEN 'font-weight: bold; background-color: red; color: black'
WHEN priority_name = '(3) Medium' AND  status_name = 'New' THEN 'background-color: yellow; color: black'
WHEN priority_name = '(2) High' AND  status_name != 'New' THEN 'background-color: red; color: black'
ELSE 'background-color: green; color: black'
END

You will then have to apply this to the “style tab”of the column we previously created in example 2 as “Calculated conditional styling” (CSS).

After doing so, you will see the following result in your report: