SAP Knowledge Base Article - Public

2954606 - Employee Central Entities OrderBy Query Option Updated to Not Supported

Symptom

  • $orderBy sorting is not followed
  • Missing / duplicate record is returned when $orderBy query option / parameter is used.

"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 HXM Suite

  • Successfactors Employee Central Odata API Entities

Reproducing the Issue

Possible Incorrect Response when $orderby is used with above entities :

 

Issue 1 : $orderBy Sorting, asc or desc subparameter, is not followed.

e.g. Query has parameter below to sort by startDate in asc(oldest to newest), but first record shows newer record than the second

 

  • Query

https://<API-Server>/odata/v2/EmpPayCompRecurring?$select=userId,payComponent,startDate&$skip=1000&$top=1000&$orderBy=startDate desc&$format=json

 

  • Response :

      "results": [

            {

                "__metadata": {

                    "uri""https://<API-Server>/odata/v2/EmpPayCompRecurring(payComponent='salary_B',seqNumber=1L,startDate=datetime'2016-12-30T00:00:00',userId=' sample_employee1')",

                    "type""SFOData.EmpPayCompRecurring"

                },

                "payComponent""salary_B",

                "userId""sample_employee1",

                "startDate""/Date(1483056000000)/" à epoch/unix time equals 12/30/2016 00:00:00 GMT

            },

            {

                "__metadata": {

                    "uri""https://<API-Server>/odata/v2/EmpPayCompRecurring(payComponent='salary_A',seqNumber=1L,startDate=datetime'1998-01-01T00:00:00',userId='sample_employee2')",

                    "type""SFOData.EmpPayCompRecurring"

                },

                "payComponent""salary_A",

                "userId"" sample_employee2",

                "startDate""/Date(883612800000)/" à epoch/unix time equal 01/01/1998 00:00:00 GMT

            },

 

Issue 2 : Missing or Duplicate expected record count.

e.g. You run a query to check how many actual records using $count parameter, in below sample total record count is 1155

  • Query :

https://<API-Server>/odata/v2/EmpPayCompRecurring/$count?$select=userId,paycomponent,startDate&$skip=0&$top=1000&$orderBy=startDate&$format=json

 

  • $count Response : 1155

       

  • Actual record response with $count parameter is only 995 or less than expected record count with page size of 1000

       

 

 

 

 Note :  Above identified issues are just samples, there are many other depending on how specific the use case is .

 

Cause

System Behavior : $orderby is no longer supported for below entities, see the update on SAP SuccessFactors Employee Central OData API: Reference Guide Section : Pagination

  • EmpJob
  • EmpEmployment
  • EmpCompensation
  • EmpPayCompRecurring
  • EmpPayCompNonRecurring
  • FOLocation
 
 

Resolution

Use Server-Based Pagination. There are two types of Server-Based Pagination. See full information on the SAP SuccessFactors HXM Suite OData API: Developer Guide for full details, See Section :Pagination

1. Cursor Based Pagination (&paging=cursor)

Sample Query of FOLocation with Cursor-based Pagination

Request:
 
OperationQuery
HTTP MethodGET
URIhttps://<API-Server>/odata/v2/FOLocation?$format=json&customPageSize=3&$select=externalCode &paging=cursor

 2. Snapshot Pagination (&paging=snapshot)

Sample Query of EmpJob entity with Snapshot-based Pagination

Request:

 
OperationQuery
HTTP MethodGET
URIhttps://<API-Server>/odata/v2/EmpJob?$format=json&customPageSize=3&$select=userId &paging=snapshot

See Also

Related KBA(s) : 

Keywords

$orderBy, pagination, duplicate or missing records, $count, incorrect sort, query option, &paging=cursor, &paging=snapshot , KBA , LOD-SF-INT-EC , Employee Central SFAPI & OData Entities , LOD-SF-INT , Integrations , Product Enhancement

Product

SAP SuccessFactors HXM Suite all versions

Attachments

Pasted image.png
Pasted image.png