Last week we discussed how to create cascading Property Control Drop downs using Data On-Demand. This is done using Information Links. If you do not use Information Links, this week’s tip will show you how to accomplish this without using Information Links. All the data will be loaded into Spotfire at once, but you can use the drop downs to select what information to show in given visualizations.
To do this we make use of Calculated Columns. Let’s assume we have the same Data Table from the previous tip, which contains ‘Region’, ‘State’, and ‘City’, along with other Columns, and we want to allow the user to select which Region to load via a Property Control, and then display States from that region in a second Property Control. Finally after selecting a State from the second Property Control, a visualization is updated to display Towns and Cities from the selected State.
First step is to create a drop-down list Property Control to display unique values from the Region column.
Next step is to create a Calculated Column called ‘Filtered State’. In the expression we should check if the Region Column value equals the value specified in the property we created earlier. If it does, it will output the value from the State Column.
if ([Region] = "${whichRegion}",[State],null)
Next step is to create the second Property Control drop-down. This one will display unique values in the newly created Filtered State Column.
The final step is to create a second Calculated Column called ‘Filtered City’. In the expression we should check if the State Column value equals the value specified in the property we just created. If it does, it will output the value from the City Column.
if ([State] = "${whichState}",[City],null)
Now we can create any visualization and use the ‘Filtered City’ column to display the Cities and Towns from the selected State from the selected Region.
Interested in learning more, take our 3.1 Delta training or our end user training courses either onsite, regionally, or using our blended online/webinar model.