‘Match Any’ Criteria in a report

When you’re filtering on a landing page in Workbooks, you can choose between Match all and Match any. However, this option isn’t available when you’re adding criteria to a report.

If you want a report to only show the rows that match either condition 1 or condition 2, you can get around this by using a Calculated Column together with a Calculated Criteria.

Step 1: Create a Calculated Column

Start by adding a Calculated Column to your report. The formula for this column will be an IF statement with a nested OR:

IF( condition1 OR condition2, 1, 0 )

This returns 1 if condition1, condition2, or both are met, and 0 if neither is.

You can extend the formula to include more than two conditions, or combine it with AND statements. For example:

• IF( (condition1 AND condition2) OR (condition3 AND condition4), 1, 0 )
• IF( (condition1 OR condition2) AND (condition3 OR condition4), 1, 0 )
• IF( condition1 OR condition2 OR (condition3 AND condition4), 1, 0 )

You can also change the values the formula returns to suit your report. For example, on a Transaction Documents report:

IF( document_date = CURDATE() OR assigned_to_name = 'John Smith', 'match', 'no match' )

This returns match if the Document Date is today, if the document is assigned to John Smith, or both. Otherwise, it returns no match.

The field names used here are examples, so you’ll need to replace them with the column names from your own report.

Step 2: Add a Calculated Criteria

Before you add the criteria, check that the formula is returning the correct value for each row. Clicking Refresh preview will show you the output.

Once you’re happy with it, go to the Criteria tab, click Add calculated criteria and select your Calculated Column. You can then restrict the report to only the rows that return the value you want.

Using the example above, if you only want to see Transaction Documents with a Document Date of today or those assigned to John Smith, set the criteria to:

calculated column = match

This also works the other way around. If you don’t want to see Transaction Documents with a Document Date of today or those assigned to John Smith, set the criteria to:

calculated column = no match

This will only show the rows that meet neither condition.

Summarized reports

Grouping is applied before calculated criteria, so if you summarize a report that includes calculated criteria, the results may not be correct. For more information, see Using Calculated Criteria.