Business Intelligence Tools

Spotfire Expressions — how to return a column name from a property control rather than the column values and a Spotfire Description hack

  • Are you having issues using $esc with property controls in custom expressions?
  • Would you like to reference a column name in an expression but keep getting the column values instead?

A short while ago I wrote a post on  how to use $esc and $map functions (https://datashoptalk.com/spotfire-expressions-esc-and-map-functions/).  More specifically, I discussed how the $esc function is simply enclosing a string (usually from a property control) in brackets so Spotfire will interpret it as a column name.   When I say “interpret it as a column name”, I mean that it will return the values in the column.  However, what if you don’t want to return the values?  What if you want Spotfire to take an action based on WHICH column has been selected from a property control.  Well, in that case the syntax is different, and I have an example for you below.

In this example, I have a text area with a drop down property control called YSelection, which is populated by Column Names, where there are two options, OilProd or c.OilProd/1000 .  On the y axis of the visualization, I have written an expression that will populate the visualization with the values from the c.Cum Oil Prod column if the column selected is Oil Prod, or it will use the c.Cum Oil Prod/1000 values on the visualization if the c.Oil Prod / 1000.

Here is the data set:

data-set

Here is the correct syntax and expression written on the y-axis:

As you can see, the key to returning the column name is using quotes with the property control and leaving out $esc.

case
when “${YSelection}” =”OilProd” then [c.Cum Oil Prod] when “${YSelection}” =”c.Oil Prod/1000″ then [c.Cum Oil Prod/1000] else ($esc(${YSelection}))
end as [Oil Prod]

Here is the property control, custom expression, and output:

solution

expression

 

I also discovered a little trick while working on this.   While testing out different pieces of syntax in duplicate visualizations, I copied and pasted the expression into the Description (Properties dialog, General submenu). This is how I discovered that you can copy and paste expressions into the Description, and Spotfire will show the result or the interpretation of the expression, which is incredibly helpful in troubleshooting.

In the screenshots below, you can see how I copied the expression into the description, and then you can see how the description interprets the property controls.  When I realized it was showing me how the control was being interpreted, I knew where my error was immediately.

The first screenshot shows the correct syntax.  The second screenshot shows what I started with.

description-problem-3

description-problem-2

Leave a Reply

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