In order to make our Connections usable quarter over quarter and year over year without making adjustments to the Query, it's necessary to use Parameters. In this Connected Learning Path, we will learn how to update our Chain to refresh a connection with a parameter between the Workiva Platform and a Wdata Query using the Connection ID.
Primary Business Use Case | Data Connectivity between Workiva and Wdata |
Primary Learning Objective | Learn how to refresh a Connection with a Parameter, setting the Parameter Value at Chain run time |
Prerequisites |
Configure Workiva Connector Complete the CLP | Refresh Connection using ID Provide permissions to the Spreadsheet for the associated Workiva OAuth Grant User |
Supporting Template | CLP | Refresh Connections with a Parameter |
Step 1: Copy Existing Query
First, we need a Query with a Parameter. We will create a copy of our existing Query and add a Parameter that let's us decide which Country we want to see total employee counts for.
- From Wdata Home, locate the Query CLP Employee Detail Query and open the menu
- Select Copy
- Update the Query Name to CLP Employee Detail by Country Query
- Click Save Query
- From the Query editing screen, click the Parameters icon on the far right panel
- Click Add Parameters and select Create New Parameter
- Name the Parameter: Country
- Switch the Pick List to enabled
- In the List Options box, list out the following Countries, each Country on its own line:
- Australia
- Brazil
- Italy
- Kenya
- United States
- Update the default to None
- Click Create
- For the Parameter to control the results of our Query, we need to connect the Parameter to the column in the source data using Filters. Navigate to the Filters tab in the Query.
- Locate the Country column from the Source Data on the left and click and drag it to the filters area on the left side
- Click on the filter value, where it says Empty String, in order to populate the filter value
- To the right of the filter value field, click on the parameters button
- Select Country
- Click Apply
-
- You now have a Query with a Parameter for Country, when you click Run Query, you will be asked which Country you want your Query results to display
- Click Save
Step 2: Create an Incoming Connection
In order to learn how to refresh a Connection using a Chain, we first need to establish a connection between a Wdata Query and a Spreadsheet.
- Navigate to the CLP Employee Overview Spreadsheet
- Add a new Sheet by clicking
- Rename it CLP Employee Details by Country
- Add an Incoming Connection to the CLP Employee Details by Country sheet:
- Open the Connections panel on the right by clicking the Connections icon
- On the Incoming Connections tab, select Add Connection
- Select Wdata Query
- Select the CLP Employee Detail by Country Query
- Set the Country Parameter to Australia
- Click Connect
- Open the Connections panel on the right by clicking the Connections icon
- There are now two sheets with Incoming Connections in the CLP Employee Overview Spreadsheet
Step 3: Edit Existing Chain
- Navigate to Chain Builder and locate the Chain CLP | Refresh Connection using ID
- Click the pencil button to edit the Chain
- Click Chain Settings in the top right
- Rename the Chain: CLP | Refresh Connections with a Parameter
- Save the Chain
Step 4: Runtime Inputs
We use the Runtime Inputs Command to kick off the Chain. This allows us to specify which Connection we want to refresh at Chain run time as well as the Parameter we want to update the value for.
- Edit the Runtime Inputs Chain Event by double clicking on the Command
- Click the Add Input button twice and add the following inputs:
- Input 1:
- Type: TextField
- Display Name: Parameter Name
- Required: Unchecked
- Input 2:
- Type: TextField
- Display Name: Parameter Value
- Required: Unchecked
- Input 1:
- Save the Command
Step 5: Refresh Connection
To account for the added Parameter, we need to update the Refresh Connections Command to account for the Parameter and Value that should be pushed through to the Query.
- Edit the Refresh Connection by double clicking on the Command
- Connection ID: Continue to use the Connection ID variable from Runtime Inputs
- Add a Parameter
- Since our Parameters are saved at the source (in the Query), we will want to populate the Parameters at the source. Click the + button next to Source Parameters
- Parameter Name: Use the Parameter Name variable from Runtime Inputs - this allows us to push the Parameter Name value specified at Chain run time through to the Refresh Connections Command
- Click on the Parameter Name field
- In the left panel, expand the Trigger dropdown
- Expand the Runtime Inputs dropdown
- Select the Parameter Name variable
- Parameter Value: Use the Parameter Value variable from Runtime Inputs - this allows us to push the Parameter Value specified at Chain run time through to the Refresh Connections Command
- Click on the Parameter Name field
- In the left panel, expand the Trigger dropdown
- Expand the Runtime Inputs dropdown
- Select the Parameter Value variable
- Save the Command
Step 6: Test the Exercise
Now that the Chain is complete, test the result
- Publish the Chain
- Click Execute and then select Run With Inputs
- Find the Connection ID
- Navigate to the CLP Employee Overview Spreadsheet
- In the Connections panel, find the CLP Employee Detail by Country Query Connection and click the up arrow. Once the dropdown appears, select View Properties
- Copy the Connection ID - make sure you don’t miss any characters or add any spaces
- Navigate back to Chain Builder and paste the Connection ID into the Connection ID Runtime Input
- Populate the Parameter inputs
- Parameter Name: the name of the Parameter in the Query - in this exercise the Parameter name is Country
-
Parameter Value: the value the Query needs to be filtered on - in this exercise we will filter to Brazil
- Note: selecting a different parameter value than what is already stored in the Spreadsheet will overwrite the Spreadsheet, we will see our data change from Australia to Brazil.
- Click Start
- Once the Chain has completed, navigate back to the Spreadsheet and see that the connection has successfully refreshed!
Successful Chain run:
Successful Connection refresh:
Learn more about refreshing Connections using Chains by completing the next path: Refresh List of Connections!