Excel Spreadsheet Automation on Anaplan Data with the QUERY Formula

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

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

  Authenticating to Anaplan

  The driver supports authenticating with Basic, Certificate, or OAuth. In every case, set Region to the region where your Anaplan account data is hosted (e.g., US1, which is the default).

  Using Basic Authentication

  Set AuthScheme to Basic, then supply your Anaplan User and Password. If your workspace uses single sign-on (SSO), you must be assigned as an Exception User to use Basic authentication.

  Using Certificate Authentication

  Set AuthScheme to Certificate, then supply the Certificate, CertificateType, and PrivateKey properties (and the matching CertificatePassword / PrivateKeyPassword if either is encrypted). The certificate must be a CA-issued X.509 certificate registered with your Anaplan tenant administrator.

  Using OAuth Authentication

  Register a custom OAuth application in Anaplan, then set the following properties:

  • OAuthClientId: The client Id assigned when you registered your custom OAuth application.
  • OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
  • CallbackURL: The redirect URI defined when you registered your application.
  • InitiateOAuth: Set to GETANDREFRESH to have the driver manage the OAuth token exchange and refresh automatically.

  See the Getting Started chapter of the help documentation for a guide to creating a custom OAuth app and using OAuth.

• 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 Anaplan data, such as Value.
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 Sales WHERE Value = '"&B6&"'","OAuthClientId="&B1&";OAuthClientSecret="&B2&";CallbackURL="&B3&";Region="&B4&";InitiateOAuth="&B5&";Provider=Anaplan",B7)


3. Change the filter to change the data.