Symptom
When a NOT IN filter operator is used to remove records with a specific value, it is also removing the records with NULL values resulting in less records in the report.
Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.
Environment
SAP Successfactors
Advanced Reporting
Reproducing the Issue
Create a Advanced Reporting Query.
Example :
1. Below query consist of two columns 1) Person Id and 2) Last Date worked.
2. You want to remove the records where Last Date Worked is 10/5/2014.
3. Apply Not In filter to remove records with this value
4. You will notice that along with the records with value 10/5/2014, all the records with NULL values are also removed.
Cause
- This is an expected behavior in Advanced Reporting which is caused because a NULL value does not equate to any other value.
- This is a special feature of NULL values.
- For example, if you have a table with two columns, and both of those columns happen to contain a NULL on the same row, and you have a filter on "fieldA = fieldB" then this filter will return FALSE. It will NOT be true.
Resolution
Workaround is to add a special filter to include NULL values if the customer wish to include them or to use Story report.
Keywords
Not In Filter Advanced Reporting NULL values , KBA , LOD-SF-ANA-ADV , Advanced Reporting (ODS) , Known Error