Using Microsoft Excel for doing queries in SAP SuccessFactors OData API

[former_member18168]

Hello SAP community,

This blog post is similar with this one, but here we will see how to achieve the solution specifically for the SAP SuccessFactors OData API.

All the images and data used here was taken from SalesDemo instance, so we are using dummy data.

Use case:

With these steps you will be able to extract data from SAP SuccessFactors OData API doing queries in Microsoft Excel, fetching the data quickly to work directly in your spreadsheet.

This sample was built with the Microsoft Excel for Office 365 version 1908.

Steps required:

1. Open Excel, go to Data tab, click in From Web



2. We are going to use one SAP SuccessFactors OData query ready for one SalesDemo instance in the DC4.

This query is returning the results in the json format due the parameter &$format=json

• Query = https://apisalesdemo4.successfactors.com:443/odata/v2/EmpJob?$top=10&$select=userId,company,costCent...

If you are not familiar building OData queries, one good way is using the SAP SuccessFactors Integration center. More details in the KBA below (follow the steps 1 to 7):

2890064 - Using the integration center tool to export the OData query and helping with your OData upserts - SAP SuccessFactors HXM Suite

Another point that you need to be aware is regarding the datacenter where your SF company is located. You can find the URLs for each datacenter in the KBA:

2215682 - SAP Successfactors API URLs for different Data Centers

3. Select the Advanced, type your OData query in URL parts field, select the HTTP request header Authorization, type Basic and your credentials. The timeout parameter is optional.



The Authorization Basic value FNGUEFSVDAz.... in the example above is the credentials base64 encoded in the following format:

username@companyID:password

To see more about this process above you can check the OData developer handbook chapter 3.1.

If you are not familiar creating SAP SuccessFactors OData API username, you can follow also the instructions of this guided answers here.

Completing the steps, press OK button. In the next screen, press connect.

4. Double click in Record



5. Double click in List



6. Click In To Table



7. In the popup, click in OK



8. This is the output (Column1), expand clicking in the button indicated:



9. In the selection, remove the _metadata and press OK



10. This is the preview. If everything is OK, click in the button Close & Load



11. This will be the final result in your spreadsheet.



The Excel will now have the data loaded in the columns/lines/table view.

The work is done 🙂

Now you are able to fetch data directly from your SuccessFactors instance to your Microsoft Excel spreadsheet using OData API.

If you would like to try some advanced steps too, you can also use the following option: Advanced Editor and manually change the Query there.

[former_member95049]

This is excellent Soliman.  Working within the integration center and being able to build the queries here makes it very easy to directly connect to Successfactors odata and pull into MS Excel.  Thank-you for your blog.  This has helped me immensely and I am certain others using Successfactors integrations will also benefit.

[former_member18168]

Hello fisker11

It is always great to work with persons like you who knows how to politely ask for assistance and work together looking forward the solution.

All the best for you 🙂

[martinstenzig]

Guilherme,

great article. I tried to import EmpPayCompRecurring into Excel, but receive an error: UnexpectedXmlAttribute : The attribute 'CollectionKind' was not expected in the given context.

Any idea?

This is the URL I used (I replaced the data center with a placeholder):

https://[our data center]/odata/v2/EmpPayCompRecurring?$select=payComponent,seqNumber,userId,startDate,lastModifiedDateTime,notes,endDate,lastModifiedBy,createdDateTime,createdOn,frequency,lastModifiedOn,createdBy,paycompvalue,currencyCode

[former_member18168]

Hello martin.stenzig3

Thanks.

Your query is correct to be executed in OData side (like Postman or other softwares), but in this blog sample we used MS Excel with the JSON format.

Try this this other query:

https://[your data center]/odata/v2/EmpPayCompRecurring?$select=payComponent,seqNumber,userId,startDate,lastModifiedDateTime,notes,endDate,lastModifiedBy,createdDateTime,createdOn,frequency,lastModifiedOn,createdBy,paycompvalue,currencyCode&$format=json

If this still cannot solve the issue, maybe it would be better to check the error in Microsoft Excel  communities.

Cheers

[martinstenzig]

I tried the json format and still get the error. Thanks for the prompt feedback and I will check on the Excel side.

[SHewetson1]

Hi Guilherme,

 

Thanks for the great article, I've managed to set an oData query up and pull the data into Excel but I am running into an issue. Do you know if there is a limitation to the number of rows that can be pulled through from SuccessFactors? I seem to only be able to pull through 1000 rows.

 

Cheers

Stephen

[former_member18168]

Hello hewe

Maximum page size is 1000 records indeed. This is something applicable in the SF OData API for client pagination or server side pagination.

It does not mean you cannot extract more than 1000 records, it means that to achieve page 2, page 3 onwards fetching the subsequent records, you will need to use client or server side pagination methods.

Please search for these keywords above in our handbook = https://help.sap.com/doc/a7c08a422cc14e1eaaffee83610a981d/latest/en-US/SF_HCM_OData_API_DEV.pdf

Thank you!

Soliman