Excel Spreadsheet Automation with the QUERY Formula

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

Getting Started

---

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

This formula requires three inputs:

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

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

  To connect to Workday, users need to find the Tenant and BaseURL and then select their API type.

  Obtaining the BaseURL and Tenant

  To obtain the BaseURL and Tenant properties, log into Workday and search for "View API Clients." On this screen, you'll find the Workday REST API Endpoint, a URL that includes both the BaseURL and Tenant.

  The format of the REST API Endpoint is: https://domain.com/subdirectories/mycompany, where:

  • https://domain.com/subdirectories/ is the BaseURL.
  • mycompany (the portion of the url after the very last slash) is the Tenant.
  For example, in the REST API endpoint https://wd3-impl-services1.workday.com/ccx/api/v1/mycompany, the BaseURL is https://wd3-impl-services1.workday.com and the Tenant is mycompany.

  Using ConnectionType to Select the API

  The value you use for the ConnectionType property determines which Workday API you use. See our Community Article for more information on Workday connectivity options and best practices.

  API	ConnectionType Value
  WQL	WQL
  Reports as a Service	Reports
  REST	REST
  SOAP	SOAP

  
  

  Authentication

  Your method of authentication depends on which API you are using.

  • WQL, Reports as a Service, REST: Use OAuth authentication.
  • SOAP: Use Basic or OAuth authentication.

  See the Help documentation for more information on configuring OAuth with Workday.

• 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 Workday data, such as Legal_Name_Last_Name.
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 Workers WHERE Legal_Name_Last_Name = '"&B6&"'","User="&B1&";Password="&B2&";Tenant="&B3&";BaseURL="&B4&";ConnectionType="&B5&";Provider=Workday",B7)
3. Change the filter to change the data.