Business Intelligence Tools

Dynamic Pivot Transformation

  • Are you constantly updating pivot transformation when column names change?
  • Do you use an interactive workflow that involves selecting columns from a property control, which then breaks your pivot transformation?
  • Would you like to make your workflow more dynamic?

I ran into an interesting situation recently while building a template.  I wanted the user to be able to choose a column via a property control to feed data into a workflow (eventually generating a waterfall chart).  That’s easy enough to do with drop down property controls.  I ran into issues with a pivot transformation in the workflow.  Pivot transformations take static column names as inputs, and I needed the pivot to change depending on what the user wants to see in the waterfall chart.  Fortunately, this is an easy problem to solve with a custom expression.

Since I am going to link to the template that I was building, I’ll begin with background on the template itself.


Use Case:

Waterfall charts are used to show the cumulative effect of positive or negative values. Users like to know, how does the total for one value (capex scenario 1) different in terms of the component parts from a second value (capex scenario 2).


Spotfire has a waterfall visualization. However, the user must configure the visualization with the column that contains the component parts on the X-Axis, which makes it impossible to show the total for one value against the total for another.  You can see that in the screenshot below.  There is no place for two totals, just one column of data.
Pre Pivot Transformation


A data function, a new table, and a few calculated column can generate the desired result.

…so that is what I was building.  Now, what users want to analyze in the waterfall may vary.  They may want to look at capex, opex, or some other type of spend, which would all be defined in different columns, so I created a drop down property control that would allow them to make a choice, as shown below.

PrePivot Transformation


Pivot Solution

In order to use either Opex, Capex, or Other farther down the workflow in a pivot transformation, I simply created a calculated column that referenced the property control.

Pivot Transformation

I will give one caveat.  I ran into an issue with this calculation.  If the column name was more than one word long, the expression was no longer valid.  I don’t know why, and I haven’t worked this part out yet.  I will update when I solve the problem.

Now, I can reference the c.Amount column rather than changing the transformation anytime I want to change my analysis.  I don’t have to touch the pivot transformation again.

Pivot Transformation

Of course, this method will also work for unpivot transformations as well.  Lastly, you may be asking, where is this template?  I wanna see it!  It’s undergoing our peer review process and will be posted on the Exchange shortly.  I’ll update this post with a link when it’s live.



2 thoughts on “Dynamic Pivot Transformation

  1. Marcin Reply

    I might be wrong but I guess you would have to use $esc(${columnSelection}) for columns with white spaces in them.

    • Julie Sebby Post authorReply

      You do need a different syntax for column names with spaces. In this particular example, I didn’t have any spaces. You can use $esc, which has the impact of adding square brackets around the contents of the document property, which Spotfire will recognize as a column name. I’ve also seen a solution that uses $csearch to replace the space with a dash, which seems overly complicated. I’ll update the post shortly. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *