This is a library of WQL queries. The queries can be used generally with any WQL tool or with the WDLookup Excel Add-in (Windows only).
Anyone is welcome to contribute to this library. Submit a pull request for review. If you do not know how to submit a pull request, paste the query into a discussion post and mention that you would like it added to the library. Please follow the query examples to include information about the query (name, description, etc.). In the future, there are plans to parse the files and enable an electronic dictionary.
In Workday, use the Convert Report to WQL report to get a headstart with query generation. This report will generate WQL from existing Workday reports.
If you will be using the WDLookup Excel Add-in, the configuration instructions are shown below.
WDLookup is a Microsoft Excel Add-in (Windows only) that enables querying data from Workday using WQL.
If you would like to use WDLookup to execute the queries from this library, download and install WDLookup from the following link.
https://github.com/swhitley/WDLookup/releases/latest
IMPORTANT: The add-in is fully-functional except that it is limited to returning 10 rows of data per query. This limitation can affect your ability to translate data using the Lookup feature. Remember that only the first 10 rows of the lookup query are returned so data outside of the first 10 rows cannot be translated.
Request a FREE registration code to unlock the ability to return all rows of data from the following site. Your information will not be sold or used in annoying spam campaigns. I ask for registration to track who uses WDLookup so that I can prioritize feature updates.
https://whitleymedia.onfastspring.com/
WDLookup requires an API client connection for Workday. This is a one-time setup requirement and requires less time than you think. ;)
The following pieces of information need to be collected during these steps:
✔️ Workday REST API Endpoint
✔️ Token Endpoint
✔️ Client ID
✔️ Client Secret
✔️ Refresh Token
-
Enable the domain: Workday Query Language and configure permissions. Users without access to the domain won't be able to make WQL API calls.
-
Turn on oAuth 2.0
Search for Task: Edit Tenant Setup - Security
-
Register an API Client for Integrations
Search for Task: Register API Client for Integrations
a. Check Non-Expiring Refresh Tokens.
b. Select System for the Scope.
c. Click OK.
d. On the next screen, scroll to the bottom of the page.
e. Copy your Client ID and Client Secret so that they can be used later.
-
View API Clients
Search for Task: View API Clients
Save the following urls for later:
- Workday REST API Endpoint - Token Endpoint
-
Click the API Clients for Integrations tab.
-
Click on the link for your newly created API Client.
-
Use the related action to click API Client, Manage Refresh Tokens for Integrations.
- Associate a Workday user with the API Client.
- At the bottom of the page, select Generate New Refresh Token.
- Copy and save the refresh token for later.
Workday Configuration Complete
-
In Excel, from the Add-ins menu, start WDLookup.
-
Click the WDLookup Settings button.
-
If the Name is not already set to "{New}", click the Add API Client button.
-
Name your API Client. You can use any name that makes sense to you.
-
Enter the API Client information from Workday in the corresponding fields.
-
Leave Access Token blank.
-
Click Save to save the API Client.
-
Click Test to verify a successful connection to Workday.
WDLookup saves the oAuth information to a settings file under %APPDATA%\WDLookup. The Client Secret is encrypted in the settings file for the specific user and machine. The encrypted data cannot be transferred to another machine due to the use of standard DPAPI encryption. Although inconvenient, greater security can be achieved by using expiring tokens. Expiration is configurable in Workday as part of the API Client settings. You can also revoke access to the API Client at any time in Workday. Security questions can be asked in the Discussions forum.
- Enter a WQL query into the query textbox.
- Click the Run button.
- The query results will be pasted into the active Excel worksheet.
- Please note that existing data in the spreadsheet will be cleared to make way for the query data.
WDLookup has a handy right-click menu that will execute the currently displayed query.
Remember, if you are using the unregistered version of WDLookup, only 10 rows of data will be returned. This applies to the lookup as well. For example, you may be able to perform a lookup on a country that starts with "A", but a country that starts with "U" may not be returned and translated.
The query results will be used to lookup the data in the highlighted cells and translate the data if it matches the first column of the query. The translation will update each selected cell with data from the second column in the query. If a translation cannot be made, the cell will be highlighted in yellow.
The reverse lookup feature reverses the process without any change to the query. The reverse lookup performs a lookup in reverse (the second field is the selector and the first field is the translation); it does not simply undo the results of the previous operation.
The Find Query feature performs a search of all saved queries. It works in coordination with the tags field on each query. When editing a query, enter a tag that will be used with the Find Query action. You can use multiple tags by separating them with a comma. When you right-click on a cell in the worksheet and select WDLookup->Find Query, the text of the cell will be used to search all queries using their tags. If a tag on a query matches the text in the selected cell, the Saved Queries selector will change and the query editor will be updated with the correct query. This enables the ability to quickly switch between queries when translating different columns of data.
Unlock WDLookup's ability to retrieve more than 10 rows of data by registering the product at the following link:
https://whitleymedia.onfastspring.com/
Follow the steps below to register WDLookup.
- Click on the button to show the registration page.
- Enter the email address that was used to purchase the registration code.
- Paste the registration code into the registration code textbox.
- Click the Save button. A dialog box will appear to confirm that WDLookup has been successfully registered.