How to set up a Chain Control Sheet ?

Background

When orchestrating reporting processes using the Data Management Suite, we often encounter complex workflow scenarios. For example, an application may need to execute:

• A single workflow multiple times with varied inputs
• Multiple distinct workflows
• Several concurrent workflows, each with specific inputs
• Source System Integration workflows requiring specific parameters, such as dates, file names, or rule names

To efficiently automate and manage these intricate workflow requirements, the concept of a Control Sheet proves to be invaluable. A Control Sheet acts as a centralized management tool that allows for the pre-definition of inputs for each workflow execution. By leveraging Control Sheets, users can specify the parameters and conditions under which each workflow should operate, thus facilitating a seamless, automated process that runs all necessary workflows with the appropriate inputs without the need for manual intervention at each step.

By creating and leveraging a Control Sheet, users can:

• Define Input Values that will be passed to a chain on each execution
• Define Flags that will determine if a chain should perform certain operations
• Pass values, such as query parameters, rule names, or date filters, into the system workflow when executing a chain or running a rule.
• Perform pieces of Roll-forward operations
• Manage the ID's for artifacts within Workiva
• Log the execution status of a workflow

This article will guide users through the process of creating a simple control sheet. This sheet will allow users to run the same workflow with various sets of inputs.

Prerequisites

• Configure the Workiva Connector
• Configure the JSON connector
• Configure the Handlebars Connector

Step 1: Setting up a Control Sheet

• Create a Spreadsheet within the Workiva platform
• Assign ownership permissions to the user associated with the OAuth2 grant.
• Rename the Spreadsheet and Sheet as 
  • Control Spreadsheet
  • Control Sheet

Step 2: Setting Headers (Keys) for the Control Sheet

Establish headers for each value that will be processed by the Chain workflow. In this example, the following headers are set up: 

• Active_Flag: To determine whether this row should be processed or not
• File_Name: Name of the file to be processed
• Table_ID: ID of the Wdata table where the file would be imported

Step 3: Assign Values to the Headers in the Control Sheet

• In this example, three values have been defined for each Key/Value pair. Therefore, as each line is read, the following data will be fed:

Step 4: Create a Chain to Retrieve Control Sheet Data

In order to retrieve the control sheet data from the spreadsheet, a Chain must be created to read the Spreadsheet. 

• Navigate to the Chains module and create a new Chain
• Navigate to the Chain settings and title your new chain Get Control Sheet Data
• Add two Chain variables:
  • cv-SpreadsheetID
  • cv-SheetName
• Populate each variable with the corresponding SpreadsheetID and SheetName values from your control sheet.

   

Step 5: Get Sheet Data

In order to use the data within your control sheet, you must use the Get Sheet Data Command. The command reads the content from a sheet in a spreadsheet and produces a .csv output. 

• Drag & drop the Workiva Get Sheet Data command onto the START section of the canvas
• Configure the command
  • Rename it Get Control Sheet Data
  • Use the chain variable cv-SpreadsheetID for the Spreadsheet ID field
  • Use the chain variable cv-SheetName for the Sheet ID/Name field
  • Enter A1: for the region
  • Leave Value Style and Revision as Calculated and -1
  • Click Save

Step 6: Convert Sheet Data to JSON

After successfully retrieving the control sheet data in .csv format, the next step is to convert it into JSON. This conversion ensures that the headers and column values are correctly reformatted into the JSON Key-Value structure. This format is crucial for the steps that follow.

• Drag the CSV to JSON command to the canvas
• Link the Get Control Sheet Data command to the CSV to JSON command
• Edit the CSV to JSON command
  • In the Input File field , use the Data output from the Get Control Sheet Data dropdown
  • Leave the Delimiter field as Comma (,)
  • Click Save

 

Step 7: Iterate through JSON Data

Now that the Control Sheet data is in JSON format, a Command Group can be used to iterate through each row of data within your control sheet

• Add Command group to the canvas
• Connect the CSV to JSON command to the In part of the Command group
• Edit the Command Group and name it JSON Iterator
  • Click on the iteration symbol to the right of the Basic Info Tab
  • Enable the iterator
  • Leave the Select Modifier Type as List
  • For the Iterations section, select the <> JSON File output from the dropdown of the CSV to JSON command
• Click Save

 

Step 8: Previewing each iteration of data

With the chain now able to iterate through each row of your control sheet, a Handlebars command can be utilized to preview the output of the information.

Note: The Handlebars command serves as a placeholder for reviewing values in this article, but other commands can also be used to process the values.

• Drag & drop a Render Text Template command to the canvas
• Link the Group Start of the Command Group to the Render Text Template command
• Edit the Command and name it Preview Control Sheet Data
• Within the Template field, enter the following:

File Name
Active Flag
Table ID 

• In order to derive the values from the control sheet, a variable transformation must be applied for each JSON iteration
• Beside File Name, choose JSON File Iteration from the sidebar, located under the Group Iterator dropdown.
  • • Click on the JSON File Iteration, select Get Value from JSON
    • In the Value field, enter the header File_Name from your control sheet
      • Note:  A good practice is to copy the header from your control sheet directly into the value field
  • Press Enter and Accept
• Repeat the above steps for Active_Flag & Table_ID headers from the control sheet 
• Once completed, the template field should look as follows:

• Save the Command

 

Step 9: Run the Chain & Preview

Now that the Chain and Control Sheet is set up, it's important to ensure that each iteration of the Handlebar output contains data for every row in the control sheet.

• Click on Publish and then Execute the chain
• Run the chain
• After the Chain is run successfully, click on the Preview Control Sheet Data command and navigate to the Log tab
  • Under "Start Rendered Text," an output for the first iteration should be visible.
  • Switch to Value 2 on the option above the Log tab to see the results for the second iteration
  • Finally, switch to value 3 to see the results of the third iteration
• Each iteration should appear as follows:

Value 1:

 

Value 2:

 

Value 3:

Leverage the Active Flag

Incorporating a Conditional logic that utilizes the value of the Active Flag is the next step, using the same chain that has been built.

• Edit the Chain and delete the link between the JSON Iterator and the Preview Control Sheet Data
• Add a Conditional command and connect the Group Start of the Command Group to the new command
• Link the Conditional command to the Preview Control Sheet Data command
• Edit the Conditional command
  • Name the command Active Flag?
  • Click the + Rule button
  • In the value field, click the <> JSON File Iteration from the dropdown of the Group Iterator
  • Add a variable transformation and use the Active_Flag header from the control sheet as the value
    • Click Accept
  • Change the condition from “Is Blank” to “=”
  • Enter  in the word “true” in the expression field
  • Click Save 

 

 

• Since the Active_Flag for two of the iterations was set to "FALSE," the Preview Control Sheet Data command executed only for the third iteration.
• Commands following the "Active Flag?" conditional command will be skipped if their iteration in the control sheet has a value of "FALSE"

Additional Use Cases

The Control Sheet offers a highly effective way to centralize, manage, and automate the values used in workflow execution. It can be applied in various scenarios, including: 

• A Central hub for managing IDs of a Source System, Workiva and Wdata elements
• Utilizing a flag to filter and take action whether a specific process should be executed
• Specifying URL endpoints for executing reports from a source system
• Defining dates and naming conventions for specific files
• Identifying file directories for an SFTP to retrieve designated files