Creates a custom schema file that executes a specific WQL query.

Basic Schemas

The connector allows you to create tables from WQL queries using the CreateWQLSchema stored procedure. With this stored procedure, you can take the output from the Convert Report to WQL task in Workday and pass it to the connector:

EXEC CreateWQLSchema TableName = 'FirstTenReports', WQL = 'SELECT reportName FROM myCustomReports LIMIT 10'

The table can be queried once the stored procedure completes. By default the table is saved to a file and can be queried on other connections with the same Location setting.

SELECT * FROM FirstTenReports

Note that tables created this way do not support any server-side query optimizations. Operations that would normally be executed by Workday, such as GROUP BY, ORDER BY, and aggregations, are instead executed within the connector. This limits their performance compared to querying WQL sources through SQL.

Parameterized Schemas

Workday reports support filtering data sources using dynamic values, but Workday does not support converting these reports directly to WQL. For example, if you create a report that limits the createdDate field to the last month, Convert Report to WQL will output WQL that contains the computed date. The results from this query will not change if you execute it again next month:

SELECT reportName FROM myCustomReports WHERE createdDate > '2024-06-26'

The connector supports WQL parameters which lift this restriction. When you create the table using a parameter, the connector exposes additional extra columns that you can use to provide dynamic values. For example:

EXEC CreateWQLSchema TableName = 'ReportsCreatedDuring', 
                     WQL = 'SELECT reportName FROM myCustomReports WHERE createdDate >= <<from_date>> AND createdDate <= <<to_date>>',
                     Parameters = 'from_date:DATE,to_date:DATE';

SELECT * FROM ReportsCreatedSince WHERE from_date_Prompt = DATEADD('month', -1, GETDATE()) AND to_date_Prompt = GETDATE()

The parameters are given as a comma-separated list, where each entry is the parameter name and parameter type separated by a colon. The connector supports the following parameter types. Make sure that the parameter type is the same as the type of the relevant field. The connector formats the value from the WHERE clause as needed (for example, dates are converted to the YYYY-mm-dd format)

• Instance (includes single-instance and self-referencing instances)
• Text
• Boolean
• Time
• Date
• DateTimeZone
• Numeric

Input

Result Set Columns