xpna.SUPPRESS FUNCTION
Overview
The SUPPRESS function is designed to monitor one or more one-dimensional ranges for specified values and then suppressing entire rows or columns based on the presence of these values. When a cell in the monitored range contains one of the suppression values the corresponding row or column will be hidden according to the range's orientation.
Important
As this formula updates the worksheet, if any of the referenced ranges (and their dependancies) use one of the volitile functions like Today(), it can cause this formula to continually re-fire.
Syntax
| Argument | Description |
|---|---|
| suppressionRange1 | An Excel range reference |
| [suppressionRange2..suppressionRange15] | Additional Excel range references (optional) |
Details
The function accepts one-dimensional ranges to clarify the intent to suppress by row or column. This structure also provides precise control over the suppression behavior for a given area. Each cell within the suppression range should contain either a formula or hard-coded value that evaluates to one of the specified suppression values to trigger suppression, or any other value to bypass it.
Note:
The suppression function re-evaluates all monitored ranges whenever any value within those ranges changes.
Working with Drill Down
When using xpna.MEMBERS with drill-down functionality, ensure that the suppression range is part of a continuous range adjacent to the drilled cell—either left/right for rows or above/below for columns—to ensure the suppression ranges accurately resize upon user drill or collapse actions.
Valid Suppression Values
Any of the following values will trigger suppression:
| Value | Description |
|---|---|
'' | A string value that is an empty string |
0 | A numeric value that is zero |
FALSE | An Excel boolean value that is FALSE |
NULL | No value, equates to NULL |
Example
The following screenshots show a straightforward of row and column suppression on a simple xpna report.
Firstly, this screenshot shows the setup of the xpna.SUPPRESS function and the report in an unsurpressed state:
This second screenshot shows the state of the report after suppression has run:
Common Issues
- The function will return an error if at least one range is not provided.
- The function will return an error if any of the specified ranges are not one-dimensional.