The CREPORTSUPPRESS formula allows you to select a range and an Axis/Axes that will have suppression applied.
To determine suppression, we look for one of the following conditions to be true:
- The cell value is null (Note: this is not the same as the text value NULL that can come back from Analysis Services)
- The cell value is “” (an empty text value)
- The cell value is 0
If all the cells in the axis we are suppressing for the given range are one of the above values, that entire row or column will be suppressed (hidden).
Another important feature of this formula is that when it is shown in a published report, it will display as a drop list that you can use to change the suppression being applied on the fly for your report.
See below for more examples.
CREPORTSUPPRESS (SuppressionType, CalumoFormulaOnly, OnlyGiveOnOffChoiceInList, SuppressionRange1,…,SuppressionRange20)
(Required) Specify the axis that you want to suppress on. The valid options are:
Default value: None
Whether or not to only look at cells with CALUMO formula in them when determining whether to suppress
Default value: false
When the formula is visible in a Published Report it renders as a drop list of items. The list with either show all the valid Suppression Types (False) or it will show only “Suppression On” and “Suppression Off” (True).
When you set this option to True, the drop list will make the behavior such that “Suppression On” will apply the current Suppression Type and “Suppression Off” will clear all suppression.
Default value: false
Select the range of cells that you want to apply suppression to.
SuppressionRange2 - 20¶
Select up to 19 more ranges that will have suppression applied to.
The benefit of having multiple ranges evaluated in one formula is when you make the cell containing the formula visible in a Published Report, the choice you make in the list will be applied to all specified ranges.
If you want to have suppression on ranges indepentend of each other, just use multiple formula.
Suppression is commonly used to make sparse data more readable by hiding all the information that has no meaning to the current set you are looking at.
Take for example a set of product data that has region specific products, when you change regions you will get lots of blank areas in your report for products that are not sold in the region you are looking at. Using suppression on this report will mean that as you change region, the products that are not sold in that region will be hidden and your report will just show you the important data.
The following example shows a worksheet with suppression off and then suppression on.
Suppression is off in this image and you can see that there are 3 columns, January, March and April that should be suppressed if we have suppression on Columns.
Suppression is on for Columns in this image and you can see that the 3 columns, January, March and April are now suppressed.
In Excel, our second example appears as
Upon publishing, it appears as
Altering to ROW AND COLUMN provides
SUPPRESSION OFF returns the view we have in Excel