Skip to end of metadata
Go to start of metadata

In some scenario, 0 in query result has a real business meaning. However when a query has two structures, in some drill down status the query result may get quite some NULL lines. With some tricks, these NULL lines can be suppressed while 0 cells are still kept with Zero Suppression function.

The trick is: 

Instead of using original key figure K, create a formula as:   K + (K==(K*0))*0.0000000001

Then set the zero suppression on both query and structure levels.

Example

A query is defined as in example 3 and gets below result:

You would like to suppress all empty lines but still keep the 0,000ST in the query result.

Define a formula F1= (Quantity == (Quantity*0)) as below:

With this formula, we could get query result like this:

According to the formula calculation rule in Note 794006, F1 returns 1 when Quantity is 0. It returns NULL when Quantity is NULL. It returns 0 for all other values.

With this, by define a formula as below we can add a very small number to Quantity=0 so that this 0 is not suppressed but still display as 0 in query result.

Set F2 display with 0 decimal.

Hide Quantity and F1, set zero suppression as below:

Now the query gets NULL lines suppressed as below:

 

SAP Consulting Notes

  • 794006 Display of zero or 0.00 with certain formula operators

 

  • No labels