Conditional Row Suppression

Conditional Row Suppression allows the user to hide rows of data that meet a predefined condition. This might be useful when certain fields do not apply to all records.

Address Example

The below example displays contact information for company employees residing in the UK and USA. The Employees.Region field stores State values for USA residents and null value for UK residents.

When building an Employee Directory report, the formula below might be used for the “Address” column to joining together each individual component of the employee’s address.

={Employees.Address} & ", " & {Employees.City} & ", " & {Employees.Region} & ", " & {Employees.PostalCode}

Here’s how this data is returned without the use of row suppression. Note that Employees.Region doesn’t return any information for Buchanan, Steven or Dodsworth, Anne but is necessary to show the State for Callahan, Laura and Fuller, Andrew.

Conditional Row Suppression can be used in conjunction with another detail row to format the Address column on the report as appropriate for each employee’s country of residence. 

1. Add another Detail row to the report and copy all of the formulas from the first one to the second.
2. Modify the formula for the second row’s Address column to replace {Employees.Region} with {Employees.Country}.
3. Select the first cell on the top detail row, and then click the Format Cell icon on the toolbar.
4. Switch to the Conditional tab of the Cell Formatting dialog.
5. Click the Add button at the bottom of the dialog to add a new condition.
6. For the Action, choose Suppress Row from the dropdown.
7. Click the Formula Editor icon to open the Formula Editor and enter this formula: Not({Employees.Country} = "USA")
8. Click Okay to close the Formula Editor and again to close the Cell Formatting dialog.
9. Select the first cell of the second detail row, and repeat steps 3–8 for the second row. At step 7, enter this formula: {Employees.Country} = "USA"

Now, whenever the employee’s country is not the USA, the first row will be hidden and the second will be displayed. When the employee’s country is the USA, the second row is hidden and the first one will be used. When this report is executed or reported, the output will now look this, with the correct fields:

Notice for the employees living in the UK, the name of the country replaces a null value. For employees living in the USA, their state of residence appears.