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.
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