Placeholder

Customer Forum

OR statement

Workbooks Support Posted: 2011-08-15 13:34

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

  • StartNew 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:

Workbooks Support Posted: Tue, 16.08.2011 - 08:33

I've almost managed to get my formula right, but am stuck on a report.  I need a report that lists organisations in the city of Berlin OR the country of Spain.

How do I achieve this please?

Workbooks Support Posted: Tue, 16.08.2011 - 08:59

You need to create a report very similar to that described above.  The two main differences are:

  • The report needs to be based on the Organisation record type, rather than Opportunities

  • The formula is slightly different due to the different columns, town and country:

IF((main_town_or_city = 'Berlin') OR (main_country = 'Spain'), 'Yes', 'No')