Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for SAP SuccessFactors provides formulas that can edit, save, and delete SAP SuccessFactors data. The following three steps show how you can automate the following task: Search SAP SuccessFactors data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

• Query: The declaration of the SAP SuccessFactors data records you want to retrieve or the modifications to be made, written in standard SQL.

• Connection: Either the connection name, such as SAPSuccessFactorsConnection1, or a connection string. The connection string consists of the required properties for connecting to SAP SuccessFactors data, separated by semicolons.

  You can authenticate to SAP Success Factors using Basic authentication or OAuth with SAML assertion.

  Basic Authentication

  You must provide values for the following properties to successfully authenticate to SAP Success Factors. Note that the provider will reuse the session opened by SAP Success Factors using cookies. Which means that your credentials will be used only on the first request to open the session. After that, cookies returned from SAP Success Factors will be used for authentication.

  • Url: set this to the URL of the server hosting Success Factors. Some of the servers are listed in the SAP support documentation (external link).
  • User: set this to the username of your account.
  • Password: set this to the password of your account.
  • CompanyId: set this to the unique identifier of your company.

  OAuth Authentication

  You must provide values for the following properties, which will be used to get the access token.

  • Url: set this to the URL of the server hosting Success Factors. Some of the servers are listed in the SAP support documentation (external link).
  • User: set this to the username of your account.
  • CompanyId: set this to the unique identifier of your company.
  • OAuthClientId: set this to the API Key that was generated in API Center.
  • OAuthClientSecret: the X.509 private key used to sign SAML assertion. The private key can be found in the certificate you downloaded in Registering your OAuth Client Application.
  • InitiateOAuth: set this to GETANDREFRESH.
• ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search SAP SuccessFactors data, such as city.
2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
=CDATAQUERY("SELECT * FROM ExtAddressInfo WHERE city = '"&B5&"'","User="&B1&";Password="&B2&";CompanyId="&B3&";Url="&B4&";Provider=SAPSuccessFactors",B6)
3. Change the filter to change the data.