The Power of Handlebars in Workiva Chains: Master Data Transformation Techniques

In the world of financial reporting and data management, flexibility and precision are paramount. Workiva Chains provides a powerful way to automate workflows, but its true potential is unlocked when combined with Handlebars templating. As someone who has worked extensively with Wdata and Chains, I’ve found that Handlebars offers an elegant solution for data transformation challenges that would otherwise require complex workarounds or manual intervention.

In this post, I’ll explore how Handlebars can be leveraged within Workiva Chains to transform data efficiently, convert columns to arrays, prepare lists for iteration, and solve common data manipulation challenges.

What Makes Handlebars Powerful in Workiva Chains and Wdata?

Handlebars is a logic-less templating language that allows you to generate HTML or JSON formats with minimal code. Within Workiva Chains, Handlebars serves as a Swiss Army knife for data transformation, offering several advantages:

1. Declarative syntax - Express what you want, not how to get it
2. Built-in iteration - Process arrays and objects with ease
3. Conditional logic - Apply transformations based on data values
4. Context awareness - Navigate complex data structures intuitively
5. Composability - Chain transformations together for powerful results

For a comprehensive reference on Handlebars syntax in Workiva, check out the official Handlebars command documentation.

Essential Workiva Chains Handlebars Techniques for Data Transformation

1. Converting Columns to Arrays in Workiva with #each

One of the most common data transformation needs in Workiva is converting column-based data into array structures. Here’s a simple example for creating a basic array:

[
  {{#each items}}
    "{{this}}"{{#unless @last}},{{/unless}}
  {{/each}}
]

EmployeeID
EMP001
EMP002
EMP003
EMP004
EMP005

[
  {
    "EmployeeID": "EMP001"
  },
  {
    "EmployeeID": "EMP002"
  },
  {
    "EmployeeID": "EMP003"
  },
  {
    "EmployeeID": "EMP004"
  },
  {
    "EmployeeID": "EMP005"
  }
]

{
  "employeeIds": [
    {{#each employeeIds_json}}
      "{{this.EmployeeID}}"{{#unless @last}},{{/unless}}
    {{/each}}
  ]
}

{
  "employeeIds": [
    "EMP001",
    "EMP002",
    "EMP003",
    "EMP004",
    "EMP005"
  ]
}

2. Converting JSON to CSV with Handlebars for Flattened Data Export

While Workiva Chains includes built-in commands for basic data conversion, there are scenarios where you need more surgical control - especially when dealing with nested objects and arrays that require specific flattening.

Handlebars excels at this precise data reshaping:

Department,Category,Value
{{#each financialData}}
  {{#each ../departments}}
    "{{lookup ../this "Department"}}","{{lookup this "Category"}}","{{lookup ../this (lookup this "Category")}}"
  {{/each}}
{{/each}}

{
  "company": "Acme Corp",
  "fiscalYear": "2023",
  "departments": [
    {
      "name": "Marketing",
      "budget": {
        "Advertising": { "planned": 120000, "actual": 125400 },
        "Events": { "planned": 50000, "actual": 48200 },
        "Digital": { "planned": 85000, "actual": 86500 }
      }
    },
    {
      "name": "Engineering",
      "budget": {
        "Salaries": { "planned": 450000, "actual": 447000 },
        "Software": { "planned": 32000, "actual": 36750 },
        "Hardware": { "planned": 65000, "actual": 61200 }
      }
    },
    {
      "name": "Sales",
      "budget": {
        "Commissions": { "planned": 175000, "actual": 182300 },
        "Travel": { "planned": 45000, "actual": 39800 },
        "Training": { "planned": 28000, "actual": 24500 }
      }
    }
  ]
}

Department,Category,Planned,Actual,Variance
{{#each data.departments}}
{{#each budget as |values category|}}
"{{../name}}","{{category}}",{{values.planned}},{{values.actual}},{{subtract values.actual values.planned}}
{{/each}}
{{/each}}

Department,Category,Planned,Actual,Variance 
"Marketing","Advertising",120000,125400, 
"Marketing","Digital",85000,86500, 
"Marketing","Events",50000,48200, 
"Engineering","Hardware",65000,61200, 
"Engineering","Salaries",450000,447000, 
"Engineering","Software",32000,36750, 
"Sales","Commissions",175000,182300, 
"Sales","Training",28000,24500, 
"Sales","Travel",45000,39800, 

3. Building API Payloads with Dynamic Keys for Workiva’s Prototype API

When integrating with Workiva’s Prototype API, Handlebars provides an excellent way to construct complex API payloads. A common use case is dynamically generating SheetUpdate objects for the updateSheet endpoint, which allows automated manipulation of spreadsheet content.

Here’s an example that generates a payload for inserting rows at multiple positions in a spreadsheet:

{
  "worksheetId": "{{worksheetId}}",
  "requests": [
    {{#each rowOperations}}
      {
        "insertRows": {
          "startRowIndex": {{startRow}},
          "endRowIndex": {{add startRow rowCount}},
          "inheritFormatting": {{formatInheritance}}
        }
      }{{#unless @last}},{{/unless}}
    {{/each}}
  ]
}

Col1,Col2,Col3
Q1 Sales,100,200
January,30,50
February,30,75
March,40,75
Q1 Total,100,200
,,
Q2 Sales,150,250
April,35,60
May,55,90
June,60,100
Q2 Total,150,250
,,
Q3 Sales,200,300
July,65,100
August,70,100
September,65,100
Q3 Total,200,300

Row Number,Col1,Col2,Col3
1,Q1 Sales,100,200
2,January,30,50
3,February,30,75
4,March,40,75
5,Q1 Total,100,200
6,,,
7,Q2 Sales,150,250
8,April,35,60
9,May,55,90
10,June,60,100
11,Q2 Total,150,250
12,,,
13,Q3 Sales,200,300
14,July,65,100
15,August,70,100
16,September,65,100
17,Q3 Total,200,300

SELECT 
  CAST(input_data.'Row Number' AS INTEGER) + 1 AS HeaderRowNum
FROM input_data
WHERE Col1 IS NULL AND Col2 IS NULL AND Col3 IS NULL

HeaderRowNum
7
13

[
  {
    "HeaderRowNum": 7
  },
  {
    "HeaderRowNum": 13
  }
]

 {
"applyFormats": {
    "formats": [
      {
        "cellFormat": {
          "backgroundColor": "#d0e0f0"
        },
        "ranges": [
     {{#each headers}}
          {
            "startColumn": 0,
            "startRow": {{this.HeaderRowNum}},
            "stopColumn": null,
            "stopRow": {{this.HeaderRowNum}}
      }{{#unless @last}},{{/unless}}
      {{/each}}
        ],
        "textFormat": {
          "bold": true
        },
        "valueFormat": {
          "valueFormatType": "TEXT"
        }
      }
    ]
    }
}

{
    "applyFormats":
    {
        "formats":
        [
            {
                "cellFormat":
                {
                    "backgroundColor": "#d0e0f0"
                },
                "ranges":
                [
                    {
                        "startColumn": 0,
                        "startRow": 7,
                        "stopColumn": null,
                        "stopRow": 7
                    },
                    {
                        "startColumn": 0,
                        "startRow": 13,
                        "stopColumn": null,
                        "stopRow": 13
                    }
                ],
                "textFormat":
                {
                    "bold": true
                },
                "valueFormat":
                {
                    "valueFormatType": "TEXT"
                }
            }
        ]
    }
}

4. Conditionally Processing data with #if and #unless

The power of conditional processing in Chains cannot be overstated. When transforming data, you often need to apply different logic based on values:

{{#each items}}
  {{#if value}}
    {{value}}
  {{else}}
    No value specified
  {{/if}}
{{/each}}

This simple pattern lets you handle missing values gracefully in Workiva Chains. The #unless helper provides the inverse functionality:

{{#unless isProcessed}}
  Item requires processing
{{/unless}}

5. Navigating Nested Data with ../

When working with nested data structures in Chains, navigating between contexts becomes crucial:

{{#each customers}}
  <h2>{{name}}</h2>
  {{#each orders}}
    <p>Order ID: {{id}}, Customer: {{../name}}</p>
  {{/each}}
{{/each}}

Combining Handlebars with Workiva’s Tabular Transformation

While Handlebars alone is powerful, combining it with Workiva’s Tabular Transformation - Advanced Query command creates a formidable data manipulation toolkit for Chains.

Example: Grouping and Aggregating Data

SELECT 
  Department,
  SUM(Amount) AS Total,
  COUNT(*) AS TransactionCount
FROM data
GROUP BY Department

Working Around Limitations in Workiva Chains Handlebars

While Workiva Chains doesn’t support custom Handlebars helpers, there are creative ways to overcome this limitation:

1. Pre-process data using Tabular Transformation commands
2. Use built-in functions creatively to achieve complex transformations
3. Break complex transformations into multiple Chain steps
4. Leverage lookup tables for mapping values

Best Practices for Handlebars in Workiva Chains

1. Start simple - Build your transformation incrementally
2. Test with sample data - Verify outputs at each stage
3. Document your approach - Complex transformations benefit from clear comments
4. Consider performance - Large datasets may require optimized approaches
5. Maintain readability - Well-structured templates are easier to maintain

For more tips, check out the Workiva Community forum where users share their experiences and solutions.

Complementary Tools: A Peek at Workiva Data Prep

While Handlebars in Chains offers powerful transformation capabilities for data, it’s worth mentioning that it’s part of a larger ecosystem of data management tools within Workiva. For particularly complex data transformation challenges, Data Prep with its pipeline functionality can be a powerful complement to Chains.

Data Prep excels at handling complex data transformations at scale, with features like mapping groups, transformation rules, and reusable pipelines that can process large datasets efficiently.

Stay tuned for our upcoming post that will dive deep into Workiva’s Data Prep capabilities and how they can work alongside Chains with Handlebars for comprehensive data management solutions.

Conclusion: Mastering Data Transformation with Handlebars in Chains

Handlebars in Workiva Chains provides a powerful, flexible approach to data transformation. By combining the declarative syntax of Handlebars with the data manipulation capabilities of Tabular Transformation commands, you can solve complex data challenges with elegant, maintainable solutions.

While you may occasionally bump against limitations of “vanilla” Handlebars, the combination of these tools offers more than enough power for most data transformation needs in Workiva. As you become more familiar with these techniques, you’ll discover that many seemingly complex transformations, like converting columns to arrays or creating JSON structures, can be accomplished with surprising simplicity.

I encourage you to experiment with these approaches in your own Workiva Chains workflows - you might be surprised at how much you can accomplish with just a few lines of Handlebars templates.