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

Basic Schemas

The provider 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 provider:

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 provider. 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 provider supports WQL parameters which lift this restriction. When you create the table using a parameter, the provider 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 provider supports the following parameter types. Make sure that the parameter type is the same as the type of the relevant field. The provider 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