|Posted: 2011-08-15 13:34|
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:
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
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:
NOTE: The currency symbol and thousand separator are not required when entering a value. Use 10000 rather than £10,000.
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
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
You may also want to take a look at some of the questions we've already answered:
|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?
|Posted: Tue, 16.08.2011 - 08:59|
You need to create a report very similar to that described above. The two main differences are: