Introduction

An OR statement allows you to match on any criteria across different fields e.g. Status = New OR Value > £5,000.

This is unlike the AND statement which matches on all criteria e.g. Status = New AND Value > £5,000.  An AND statement is generally used to return fewer results as you're reporting on a narrower result set. 

NOTE: If you have multiple criteria on the Criteria tab of a report they will be joined with an AND statement when the report is run (as per the example above).

Using an OR statement

You need to use two components to achieve an OR statement:

• An IF and OR SQL statement in the Calculated Column formula builder.

• Calculated Criteria to filter on the Calculated Column.

Worked Example

Scenario: You want to report on all orders that are either at Opportunity stage "Stage 5 - Closed Won" OR have an amount greater than £10,000.

Step 1 - Create a simple base report to start from

• Start > New Report > "A new blank report" > Opportunities.

• Add the Opportunity Name, Opportunity Stage and Amount columns.

• Click "Refresh preview".

NOTE: The count in the bottom right corner indicates the report has returned 63 opportunities.  This includes opportunities that don't fit the scenario described above, so a bit more work is required.

Step 2 - Add the Calculated Column

The Calculated Column will use a formula and output a value of "Yes" or "No" to indicate whether or not each Opportunity is at "Stage 5 - Closed Won" OR is over £10,000:

• Click "Add calculated column".

• Name the column.

• Copy and paste or type the following formula into the Formula field.

  • IF((amount > 10000) OR (opportunity_stage_name = 
    'Stage 5 - Closed Won'), 'Yes', 'No')
    
    

NOTE: The currency symbol and thousand separator are not required when entering a value.  Use 10000 rather than £10,000.

• Click "Save & Close".

• Click "Refresh preview".

Each row has now been "categorised" with a value of "Yes" or "No" if they are at "Stage 5 - Closed Won" OR over 10k. 

Great!  We just need to add a criteria to the limit the rows returned by the report.

Step 3 - Add a Calculated Criteria to make the report display only the Opportunities that you're interested in

• Select the Criteria tab and click "Add calculated criteria".

• Select the Yes/No column, select the "is" Operator and set the Text to "Yes".

• Save & Close.

• Refresh preview.

The report is now complete.  It is making use of the OR SQL statement and listing opportunities that are either at Opportunity stage "Stage 5 - Closed Won" OR have an amount greater than £10,000:

Step 4 (Optional) - Hide the Yes/No column to improve the report's appearance

• Hover over any column header.

• Click the down arrow.

• Deselect the Yes/No column.

You may also want to take a look at some of the questions we've already answered:

• Reporting on opportunities closing this month or later

• Example IF statement

• How to count records

• Reporting Tips & Tricks: How to "slice & dice" data

• How to calculate age