Excel Spreadsheet Automation on Sage X3 Cloud Data with the QUERY Formula

The CData Excel Add-In for Sage X3 Cloud provides formulas that can query Sage X3 Cloud data. The following three steps show how you can automate the following task: Search Sage X3 Cloud 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 Sage X3 Cloud data records you want to retrieve, written in standard SQL.

• Connection: Either the connection name, such as SageX3CloudConnection1, or a connection string. The connection string consists of the required properties for connecting to Sage X3 Cloud data, separated by semicolons.

  Sage X3 Cloud uses the OAuth 2.0 Client Credentials flow, and an X-API-Key is also required for API access. Set AuthScheme to OAuth and specify the following connection properties:

  • URL: The base URL of your Sage X3 Cloud instance.
  • OAuthAccessTokenUrl: The OAuth token endpoint (e.g., https://your-auth-domain/oauth/token).
  • OAuthClientId: Your OAuth application client ID.
  • OAuthClientSecret: Your OAuth application client secret.
  • Audience: The API audience value for the token request.
  • XAPIKey: The X-API-Key provided by your Sage X3 Cloud administrator.
  • Folder: The Sage X3 folder name (e.g., SEED). This folder is used as the default schema.
  • Folders (optional): A comma-separated list of Sage X3 folders (e.g., SEED,PERF). Each folder is exposed as a separate schema, so you can query across folders with the Schema.Table syntax.

  The driver obtains an access token with the Client Credentials flow and sends it with the X-API-Key on every API request. With InitiateOAuth set to GETANDREFRESH (the default), the driver acquires and refreshes the token automatically.

• 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 Sage X3 Cloud data, such as BPCNUM.
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 BPCUSTOMER WHERE BPCNUM = '"&B9&"'","AuthScheme="&B1&";URL="&B2&";OAuthAccessTokenUrl="&B3&";OAuthClientId="&B4&";OAuthClientSecret="&B5&";Audience="&B6&";XAPIKey="&B7&";Folder="&B8&";Provider=SageX3Cloud",B10)


3. Change the filter to change the data.