Workday: How to Access Live Data from the WQL for a Workday Report

The CData Workday Drivers and Connectors now support creating tables that execute specific Workday Query Language (WQL) statements for Workday reports. The new stored procedure, CreateWQLSchema, takes the output from the "Convert Report to WQL" task in Workday and uses the WQL statement to return the same data as the report in Workday.

In this article we describe the CreateWQLSchema stored procedure, showing how to take the output from the "Convert Report to WQL" task in Workday and create a new table based on the report from Workday.

How to use the CreateWQLSchema Stored Procedure

The CreateWQLSchema stored procedure is an effective feature that allows users to define and create custom tables that execute specific Workday Query Language (WQL) statements in Workday. By leveraging WQL, the CData Workday driver can extract data that mirrors the results of Workday reports while reaching better performance than the Reports as a Service (RaaS) method.

When the stored procedure is executed, a schema file is generated that represents the structure of a new table based on the WQL statement that was passed. This simplifies the process of querying reports in Workday, while optimizing performance for quicker access to report data.

Learn more about the CreateWQLSchema stored procedure and explore additional features of the CData Workday Driver by visiting the Workday Driver documentation page.

Use the "Convert Report to WQL" task in Workday

In Workday you can use the "Convert Report to WQL" task to return a WQL query that is used to return the same data as the report itself. Below are the steps to run the task in Workday:

1. Search for and open the "Convert Report to WQL" task in Workday:
2. Select which report you would like to convert to WQL:
3. If required, select the filters for the report:
4. The WQL Query String for the report that was converted is returned and can be copied for use with the CreateWQLSchema stored procedure:

Basic Schemas

For reports that do not require filtering using dynamic values, you can simply copy and paste the output from the "Convert Report to WQL" task in Workday and pass it to the driver. Below is an example of the store procedure being run:

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

After the stored procedure completes, you can now query the newly created table:

SELECT * FROM FirstTenReports

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, the "Convert Report to WQL" task outputs WQL that contains the computed date. For example:

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

The results from this query will not change if you execute it again next month. To work around this, the CData Workday driver 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';

After the stored procedure completes, you can query the table that was created and provide values for the parameters that were set:

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

Free Trial & More Information

Thanks to CData connectivity solutions, working with your Workday data has never been easier, whether you are working in on-premise BI, reporting, ETL or custom applications through Workday drivers and connectors or replicating Workday data to a database, data lake or data warehouse using CData Sync. Download a free, 30-day trial of any of our solutions (or start a free trial of CData Sync) and start getting more out of your Workday data today.