SAP Knowledge Base Article - Public

2500698 - Suppress Zeros Checkbox is not selected in AO even it is selected in Query Designer or Modelling Tool

Symptom

  • The "Suppress Zeros" settings are set in the "Query Properties" in Query Designer or Modelling Tool.

    In Query Designer:

        a2.jpg

    In Modelling Tool:

        a6.PNG

  • When open the query in AO, in section "Zero Suppression", the checkbox "Suppress Zero in Rows" or "Suppress Zero in Columns" are not selected.  
    In AO:

    a1.jpg

Environment

  • SAP Analysis for Microsoft Office

Reproducing the Issue

  1. Create a query in Query Designer or Modelling Tool and select "Suppress Zeros" for Rows or Columns.
  2. Open the query in AO.
  3. Check the checkbox "Suppress Zero in Rows" or "Suppress Zero in Columns" in "Analysis" tab of Display Panel.

Cause

1) This is not an error.
The BEx Query has 2 settings relevant for zero suppression in Query Designer or Modeling Tool.
The first one is available at the overall query level. It determines whether zero suppression is active or not in general. See the first and the second screenshots in Section Symptom.
The second one is available at the structure level. It specifies the zero suppression behavior of the structure. See the screenshots below.

In AO:

    a3.jpg

In Modeling Tool:

    a7.PNG

Given the 2 settings there are several combinations that can be set for each axis respectively (especially if there are 2 structures).

To simplify the user interface (UI), Analysis Office only allows to switch on / off zero suppression for each axis in general.

Hence there is only 1 setting with 2 options for each axis available.

The checkbox "Suppress Zero in Rows" or "Suppress Zero in Columns" in "Analysis" tab of Display Panel in AO will be selected ONLY in the combination [Zero Suppression:On - Structure As Group:Off].

2) All combinations set to the BEx query in Query Designer or Modeling Tool will be taken into account in AO. It means for the combination [Zero Suppression:On - Structure As Group:On], even the checkbox "Suppress Zero in Rows" or "Suppress Zero in Columns" is not selected in "Analysis" tab of Display Panel, the "Zero Suppression" will be executed.

For example, for the result below:

    a4.jpg

If the combination is [Zero Suppression:On - Structure As Group:On], the column will be suppressed as below screenshot, but, "Suppress Zero in Columns" is not selected in "Analysis" tab of Display Panel as the same as the third screenshot in Section Symptom.

    a5.jpg

3) After the code correction in Note 2366950 (contains in 2.4 SP3 and 2.5 SP0), if the default combination is [Zero Suppression:On - Structure As Group:On] and you want to click the checkbox to change it to [Zero Suppression:On - Structure As Group:Off] in the runtime in AO. The checkbox needs to be clicked twice. After the first click, the combination will be set as [Zero Suppression:Off - Structure As Group:Off] (zero suppression is turned off completely), and you will see an Information message:

Suppress Zeros is turned off. To turn it on, choose Suppress Zeros again. For a detailed explanation, see Note 2366950 or the User Guide. (ID-107024)

    a8.jpg

Resolution

  • Set 'Suppress Zeros' settings in query property and structure property in Query Designer or Modeling Tool as your want. It will work as expected in AO.
  • To change the Suppress Zeros setting in AO, click 'Suppress Zeros' options according to the steps explained in Section Cause, point 3.

See Also

SAP Note 2366950 - Zero Suppression only active after second activation

Keywords

AO, Analysis for Office, Analysis for Excel, Suppress Zero, zero suppression , KBA , BI-RA-AO-XLA , Excel Addin , BW-MT , Modeling Tools (Eclipse) , Problem

Product

SAP Analysis for Microsoft Office all versions