Skip to Content
Technical Articles
Author's profile photo Guilherme Soliman

Using Microsoft Excel for doing queries in SAP SuccessFactors OData API

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,costCenter&$format=json

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.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Scott Fisk
      Scott Fisk

      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.

      Author's profile photo Guilherme Soliman
      Guilherme Soliman
      Blog Post Author

      Hello Scott Fisk

      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 🙂

      Author's profile photo Martin Stenzig
      Martin Stenzig

      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

      Author's profile photo Guilherme Soliman
      Guilherme Soliman
      Blog Post Author

      Hello Martin Stenzig

      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

      Author's profile photo Martin Stenzig
      Martin Stenzig

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