This guide provides step-by-step instructions for implementing the Run Query & Download Results with Control Sheet template. This template serves as an alternative to the Run Query command, which previously required users to manually enter parameters in chains before deployment and execution.
With the use of a control sheet, users can add multiple query IDs and define parameters, such as Pick-list or Multi-Select, dynamically. This template is commonly integrated into existing chains but can also be used in any workflow that requires query results in CSV format.
Prerequisites
The following connectors must be enabled and configured in your workspace.
- Configure the Workiva Connector
- Configure the JSON connector
- Configure the Handlebars Connector
- Configure the HTTP Request Connector
- Configure the Tabular Transformation Connector
The following workspace variables must be created within your workspace:
- wsv_API Base URL - IAM
- wsv_Client ID
- wsv_Client Secret
- wsv_API Base URL - Wdata
Find the template
The Run Query & Download Results with Control Sheet template is located in the Workiva Chains section of the Templates screen.
Here's how to find it:
- In Chain Builder, go to the Templates tab.
- Select Workiva Chains from the menu at the top.
- Search for 0.0 Execute a Query | Top-Level Orchestration at the top right and open the template.
-
After locating the template, click New Chain to deploy it to your workspace.
Note: Once configured, each chain in this template must be published to the workspace.
Variables
| Variable type | Variable name | Description |
|---|---|---|
| Workspace variable | wsv_API Base URL - IAM |
Enter the base URL for the API based on your region:
|
| Workspace variable | wsv_Client ID |
Enter the Client ID which you receive when setting up an API grant. See here for more instructions for the Workiva API. |
| Workspace variable | wsv_Client Secret |
You can View your API grant secret by going to the Security tab in your user profile, and then clicking the Regenerate option in the Actions dropdown next to the grant. See here for more instructions for the Workiva API |
| Workspace variable | wsv_API Base URL - Wdata |
Enter the base URL for the API based on your region: |
Runtime Inputs
| Runtime input | Purpose | Field type | Required |
|---|---|---|---|
| Spreadsheet ID | This is where the control spreadsheet is located. | Text | Yes |
| Sheet ID | This is where the control sheet is located. | Text | Yes |
Configure the template
This template consists of two chains, the 0.0 Execute a Query | Top-Level Orchestration and the 0.1 Execute a Query | Download Results. When the primary chain is deployed in your workspace the chain chain is automatically deployed with it.
- Top-Level Orchestration: Based on the information from the control sheet, this chains determines whether a query uses a single parameter type (either Pick List or Multi-Select) or multiple parameter types (both Pick List and Multi-Select).
- Execute a Query | Download Results: This chain runs a query using the specified parameters and retrieves the corresponding data. Once the query is executed, the results will be downloaded in CSV format for further analysis or use.
Once the control sheet is configured and the chain template is deployed in your workspace, all you need is the Spreadsheet ID and Sheet ID of the control sheet.
These values must be added as runtime inputs for the Top-Level Orchestration chain.
Control Sheet Configuration by Parameter Type
The way you configure the control sheet depends on the parameter types defined in your Wdata query. If a query includes multiple parameter types (for example, both Pick List and Multi-Select), each parameter must be defined on a separate row using the same Query ID. If a query uses only one parameter type, the control sheet requires a single row for that query.
The examples below illustrate both scenarios.
Example: Query with Multiple Parameter Types
For a query that requires both Pick List and Multi-Select parameters, the control sheet should include separate rows for each parameter, all using the same Query ID.
Note: The Key column refers to the Parameter Name defined in the Wdata query.
Example configuration:
| Query ID | Type | Key | Values |
|---|---|---|---|
| Wdata Query ID | Pick-List-Parameters | Year | Specify a single default value (e.g., 2023). |
| Wdata Query ID | Multi-Select-Parameters | Account Type | Enter multiple acceptable values, comma-delimited (e.g., Asset, Expense, Equity, Revenue). |
Example: Query with a Single Parameter Type
For a query that uses only one parameter type, the control sheet should contain a single row for that Query ID.
For example, if the query only uses a Pick List parameter:
| Query ID | Type | Key | Values |
|---|---|---|---|
| Wdata Query ID | Pick-List-Parameters | Year | Specify a single default value (e.g., January). |
Ensure the control sheet is configured accurately, as incorrect setup may lead to downstream issues.
Loading Data into a Table
You can automate the process of loading your output directly into a table. To load the output data into a table, consider using the "Load Data to Wdata" template. This can be triggered as part of your process by adding a Run Chain event at the HTTP connector which download results.
Troubleshooting
To view your chain run history, including errors:
- Go to the Monitor tab in Chain Builder.
-
Hover over the question mark to view the status of the chain run.
If your chain triggered an error or failed Download a query result, perform the following checks:
- Verify that both chains have been published to your workspace. Each chain must be published separately.
- Ensure that the query in Wdata is updated to the latest version.
- When inputting the Query ID, Type, Keys and Value in the control sheet ensure that the entire ID has been entered and there are no leading or trailing blank spaces. Also, confirm that the Keys and Values correctly match the parameters used in Wdata.
- Ensure the OAuth2 grant associated with the Workiva connection has appropriate access.
- Make sure that all parameters used in a query is included in the control sheet.
Additional Resources
- Workiva connector
- File Utilities connector
- JSON connector
- Tabular Transformation connector
- Run a chain within another chain ("Run Chain events")
- OAuth2 Grants
- Wdata feature and API limits