‘Match Any’ Criteria in a report

When filtering on a landing page in Workbooks, it is possible to choose between ‘Match all’ and ‘Match any’. However, it is not possible to do this when adding a criteria to a report.

If you ever find that you want to only show the rows in a report that match either condition 1 or condition 2, here is what to do:

Step 1: Create a Calculated Column

You will need to created a Calculated Column. The formula for this column will be an IF statement, with a nested OR:

IF( condition1 OR condition2, 1, 0 )

This would output a ‘1’ is either condition1 or condition2 are met, or both, and will output a ‘0’ if not.

This formula can be amended to contain more than 2 conditions, or to contain 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 amend this according to your report needs, for example:

IF( document_date = CURDATE() OR assigned_to = ‘John Smith’, ‘match’, ‘no match’ )

would output ‘match’ if the Document Date of a Transaction Document is today and/or it is assigned to John Smith.

Step 2: Add Calculated Criteria

Once you are sure that this formula is outputting the correct value for each row, you can then add a Calculated Criteria, to restrict the report to only the rows that output the value you want. If we only wanted the Transaction Documents with a Document Date of today or those assigned to John Smith, then we would set the criteria to

calculated column = match

Of course we can do this the other way around; if we don’t want to see those with a Document Date of today or those assigned to John Smith, we can choose

calculated column = no match