Differentiate Null/Blank & Zero Values @ SAP BEx Level

[former_member560469]

Summary

This article contains the step by step process to differentiate null/blank value from the original 0 values.

Author:         Mitul Jhaveri

Company:     Accenture

Created on:  02 March 2018

Summary:     I am having 5 years of SAP BW support+implementation experience and developed multiple reports to users which help them to reduce manual efforts. During one of my development, I faced the challenge at BEx query level where system can not differentiate null value and zero separately. Fortunately I found below solution which I want to publish here so that every one can use that as and when they will have this kind of requirement.

Introduction:

We can’t differentiate null/blank value and zero at SAP BEx level and we don’t have any function available like “is null” in Query Designer. Query Designer will consider both null & blank as zero for any condition or further calculation. This will provide inappropriate output where we want to consider only zero, not blank/null value in our calculation.

To overcome this scenario, we need to follow below mentioned steps at SAP BEx level which will help us to differentiate Blank/Null values than the Actual Zero value.

Scenario:

We want to show only those lines into the report output where one or more than one week is having stock days is “0 or less than 3”.

Below attached is the screen shot based on which I can explain you guys what exactly we want to show in the BI report but what SAP is showing into BI report output.

BW Formula for Condition: If any of the week is having value less than 3, system will set one indicator 1 else system will set indicator 0.



 

BW Condition:

BW Report output will only show those materials where indicator is "1".



BW Report Output:



As per condition and available data, Material A3 & A5 should not be available into report output as 9^th week is having values greater than 3 and for 10^th week values are Blank/ Null/ Not available for both the materials.These materials should be removed from the output.

Procedure

Step-1: Create Formula based on your key figures with the “DELTA” function which will return 1 if key figure value will be “0” else it will return 0.

Blank will return 0 while original 0 will now return 1.

 

Step-2: Modify the previous formula using this delta output which will help us to distinguish blank/null value from the original 0.

Formula: If delta-9 or delta-10 is having value equal to “1”, system will set indicator 1. Else system will check weather material is having stock values less than 3 and which is not equal to 0 (because 0 is already considered with delta function). If this condition satisfies than system will set indicator as 1 else it will set indicator 0. (here system will set indicator 0 for A3 & A5 as, having values respective 3 & 4 for 9^th week and blank/null value for 10^th week.)

 

Note: below mentioned screenshot has been taken by deactivating condition.

 

 

 Result: After this new formula, now report will not show A3 & A5 material as per above mentioned condition – “Stock Days Condition”, as both the materials are having values greater than 3 for 9th week and blank/null values for 10th week. This blank/null value now will not be considered as 0.



Thank You.

I hope you like this blog and helpful where you want to differentiate blank/null value from the actual zero values.