Excel Spreadsheet Automation on JD Edwards Data with the QUERY Formula

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

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

  The driver connects to JD Edwards through your Application Interface Services (AIS) Server. Set the following connection properties:

  • URL: The base HTTPS URL of your AIS Server (e.g., https://jde-ais.example.com:8300).
  • User: Your JD Edwards username.
  • Password: Your JD Edwards password.
  • Environment (optional): The JD Edwards environment to use (e.g., PD920 for production or DV920 for development). If not specified, the AIS Server's default environment is used.
  • Role (optional): The JD Edwards role for the session. If not specified, the AIS Server's default role is used.
  • DeviceName (optional): An identifier for the connecting device or application, used for auditing and logging on the AIS Server.
  • Jasserver (optional): The specific Java Application Server (JAS) instance to route requests through, useful in clustered environments.

  Choosing Which Data Is Exposed

  JD Edwards organizes tables and business views by System Code, and the driver exposes each System Code as its own schema. Use these properties to control which schemas are available:

  • DataModel: One or more ERP modules (comma-separated) whose System Codes are exposed as schemas, or All to expose every System Code in the connected instance. Defaults to FinancialManagement.
  • SystemCodes: A comma-separated list of additional System Codes to expose alongside those from DataModel (e.g., 42,43).

  When you connect, the driver sends your credentials to the AIS Server to obtain a session token and caches it. The driver requests a new token automatically before the session expires.

• 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 JD Edwards data, such as BusinessUnit.
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 AccountsPayable.AccountLedger WHERE BusinessUnit = '"&B4&"'","URL="&B1&";User="&B2&";Password="&B3&";Provider=JDEdwards",B5)


3. Change the filter to change the data.