SAP Knowledge Base Article - Public

2368459 - Advanced Reporting - NOT IN filter operator removing records with NULL values

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.

Capture.PNG

 

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

2.PNG

 

4. You will notice that along with the records with value 10/5/2014, all the records with NULL values are also removed.

3.PNG

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

Product

SAP SuccessFactors Workforce Analytics all versions