Comprehensive Workday Connectivity through Workday WQL and Reports-as-a-Service

The CData connectivity solutions for Workday enable seamless integration of Workday data in modern reporting, analytics, ETL, and custom applications. CData's standards-based drivers and connectors include built-in support for Workday WQL and Reports-as-a-Service, enabling any user to work with exactly the Workday data they need in their preferred tools, applications, and platforms.

In this article, we walk through the complex querying available via CData's support for Workday WQL and walk through configuring your Workday instance and your CData solution to enable CData's support of Workday WQL and Reports-as-a-Service.

Contents

1. Powerful Querying through Workday WQL
2. Connecting to the Workday WQL Service
3. Connecting to Workday Reports-as-a-Service

Powerful Querying through Workday WQL

The Workday WQL service enables high-performance querying of Workday data. Each CData Workday connectivity solution has a built-in SQL Engine that is optimized to work with Workday WQL. As such, CData solutions are able to push the complexities of queries for data down to Workday for processing. This results in faster responses to queries and less reliance on client-side resources for data processing. Supported SQL includes:

• Naming specific fields (columns) in SELECT statements
• SQL WHERE clauses to filter results
• SQL GROUP BY statements
• SQL ORDER BY statements
• SQL LIMIT
• SQL aggregations
  • COUNT
  • COUNT(DISTINCT)
  • AVG
  • MIN
  • MAX
  • SUM
• SQL inner and left JOIN statements

Connecting to the Workday WQL Service

1. Enable the Workday WQL service before connecting from CData solutions:
   1. Log into your Workday instance, enter "View Domain" in the search bar, and click the View Domain link.
   2. Enter "Workday Query Language" in Domain prompt and click OK.
   3. Ensure that your user is included in one of the Allowed Security Group Types.
2. Create a custom OAuth application for authentication through CData connectivity solutions:
   1. Log into your Workday instance.
   2. Search for "Register API Client". This takes the user to a form.
   3. Enter a name for the app in Client Name.
   4. Choose Authorization Code Grant for the grant type.
   5. Choose Bearer as the access token type.
   6. Choose a Redirection URI:
      • For desktop/on-premise applications, use https://localhost:33333.
      • For CData Connect Cloud, or a web-hosted application like CData Sync, use https://oauth.cdata.com/oauth.
      • For a custom web application, use your own URI (e.g. https://my-domain.com/oauth). Workday will deliver verifier tokens to this URL when users login. Refer to the "Custom Credentials" section of the CData help documentation for more information.
   7. In the Scope section, enable the Custom Objects > System scope. The driver uses system endpoints to list views and columns.
   8. Add any additional desired scopes for the driver to have acecss to. If there is any uncertainty regarding which scopes to include, all the scopes under each subsection can be enabled.
   9. Check the Include Workday Owned Scope box.
   10. Click OK.
   11. Save the Client ID and Client Secret from the View API Client page for use when connecting to Workday from your CData solution.
3. Create a connection string or enter connection properties to connect to Workday:
   • OAuthClientId: Set this to the Client ID obtained from the View API Client page.
   • OAuthClientSecret: Set this to the Client Secret from the View API Client page.
   • Tenant: Set this to the tenant for the account.*
   • Host: Set this to the host for the API URL.*
   • UseWQL: Set this to "true."

   *NOTE: To find the Host and Tenant, log into Workday and search for "View API Clients." In the listed values, find the Workday REST API Endpoint. It will look something like: https://wd3-impl-services1.workday.com/ccx/api/v1/mycompany. In this case the value for Host is https://wd3-impl-services1.workday.com and the value for Tenant is mycompany.

Connecting to Workday Reports-as-a-Service

To read reports exposed the through the Workday Reports-as-a-Service, set the UseWQL property to true in the CData solution and create a custom report to use this feature.

1. In Workday, open the Create Custom Report task.
2. Name the report Reports as a Service.
3. Enter Advanced for the report type.
4. Select Enable As Web Service.
5. Clear Optimized for Performance.
6. Enter All Custom Reports for the Data Source.

After the report is created, configure the report by adding columns and filters:

1. Add 4 Columns: Fields, add Report Name, Web Service Namespace, Report Owner, and Brief Description.
2. Set "Business Object" to Custom Report (select Primary Business Object, Custom Report)
3. Open the Filter tab and create 2 filters, using the And condition:
   • Enter "Web Service Namespace" as the field and "is not blank" as the operator.
   • Enter "Current User" as the field, "in the selection list" as the operator, "Value from Another Field" as the comparison type, and "Authorized User" as the comparison value.
4. Click "OK" to save the report and click to dismiss any warnings.

The final step to accessing Reports as a Service is finding the URL associated with the report. This URL is used for the CustomReportURL connection property in your CData connectivity solution.

1. Open the newly created report.
2. Click the ellipsis (...) button beside the report title.
3. Find the Web Services tab within the pop-up.
4. Hover over the Web Services tab and select "View URLs."
5. Right-click and copy the Workday XML URL.

At this point, you will be able to read data from any Custom Reports created in Workday.

Free Trials & 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, replicating Workday data to a database, data lake or data warehouse using CData Sync, or connecting cloud platforms and applications to Workday through CData Connect Cloud. Download a free, 30-day trial of any of our solutions (or start a free trial of CData Connect Cloud) and start getting more out of your Workday data today.