Parameters in Wdata Queries are what make Queries dynamic and usable across reporting periods. In this Connected Learning Path, we will modify our Query by adding a Parameter, making it dynamic, and then edit our Chain to account for the Parameter. Then we'll export the Query results to a Workiva Spreadsheet.
Primary Business Use Case | Reporting on data stored in Wdata |
Primary Learning Objective | Executing a Query with a Parameter from a Chain |
Secondary Learning Objectives | Creating a Parameter in a Query |
Prerequisites |
Complete the CLP | Create a Query with a Calculated Field Complete the CLP | Run a Query and Export Results Identify the Query ID of the CLP Employee Detail QueryOptional - Identify the URL of an existing Spreadsheet to write the results to |
Supporting Template | CLP | Run a Query with a Parameter |
Step 1: Edit Existing Query
First, we need to edit our CLP Employee Detail Query to add a Parameter. We will create a Parameter that let's us decide which Country we want to see total employee counts for.
- From Wdata, open the Query CLP Employee Detail Query to edit it
- Follow the CLP | Create a Query with a Calculated Field to create the Query
- In the far right panel, click the Parameters icon
- 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: Edit Existing Chain
- Navigate to Chain Builder and locate the Chain CLP | Run a Query and Export Results
- Click the pencil button to edit the Chain
- Click Chain Settings in the top right
- Rename the Chain: CLP | Run a Query and with a Parameter
- Save the Chain
Step 3: Runtime Inputs
We use the Runtime Inputs Chain Event to kick off the Chain. This allows us to prompt for the ID of the Query to execute, the value of its Parameter, and for the URL of an existing Spreadsheet to write the results to. The Spreadsheet URL field is optional; if it's left blank, a new Spreadsheet will be created.
- Edit the Runtime Inputs Chain Event by double clicking on the Command
- Click the Add Input button and add the following input:
- Type: TextField
- Display Name: Query Parameter Value
- Required: Checked
- Optionally, drag and drop this new input so that it appears after the Query ID input and before the Spreadsheet URL input
- Save the Command
Step 4: Run Query
In order to retrieve the most recent data, the Query first needs to be run. Once the Query has successfully run, we can export the Query results to use in reporting down the line or to send to other external systems. We will use the Run Query Command to execute the Wdata Query.
- Double click the Run Query Command to configure it
- Click on the ADD button in the Parameters section
- In the Name field, type in the Name of the Query Parameter, in this case our Parameter is named Country
- In the Type field, select the Query Parameter Type, in this case our Parameter type is Text
- Click on the Value field
- Within the left Variables panel, click the down arrow to expand Trigger
- Click the down arrow to expand Runtime Inputs
- Click on Query Parameter Value
- Save the Command
Step 5: Test the Exercise
Now that the Chain is complete, test the result.
- Publish the Chain
- Click Execute and then select Run with Inputs
- Add values for your Runtime Inputs
- Query ID - ID of the CLP Employee Detail Query in Wdata
-
Query Parameter Value - the value you would like your Query to filter to
- In this example - we're filtering the data to Brazil
- Spreadsheet URL - full URL of the Spreadsheet you would like to export the Query results to
- Once the Chain has successfully completed, view the Spreadsheet to verify results
- The chain should have successfully run the Query and exported the results to the Spreadsheet
- You can verify the data was exported into your Spreadsheet and filtered correctly by navigating to the Sheet URL
To learn more about using Chains to streamline data centralization, check out the rest of the Data Collection Paths!