IronPython script to Set Custom Sort Order

Introduction

The Spotfire® API allows users to set custom sort orders for columns in the data table.

Set Custom Sort Order

The below script reads a custom sort order from a column in Spotfire data table and applies the custom sort order to the column in the main data table. This is particularly useful when you want to order the bars in a bar chart by something other than a numerical value (or by size). It is also useful where the set of possible values for the custom sort order is not known during the authoring of a Spotfire file.

To further explain - imagine the main data table has many rows containing sales information for different types of fruit. The need is to sort the "Fruit" column (where "Fruit" contains the name of the fruit) in that table by the size of the fruit, but that's not possible as the size information is not present in that data table.

Alphabetically sorting fruits by their name does not work either! Apple, Grape, Pear, just isn't correct.

So - another data table contains the fruit and nominal diameter. This script will read the data from the secondary table to build a list (in order of diameter). It will then apply the order of that list to the  Fruit column in the main data table.

As a secondary function, the script will also colour bars in a bar chart according to data stored about the fruit in the secondary data table.

Before

After

Script

# Copyright © 2017. TIBCO Software Inc.  Licensed under TIBCO BSD-style license.

# Andrew Berridge, TIBCO Software, 2015
from Spotfire.Dxp.Data import *
from System.Collections.Generic import List
from Spotfire.Dxp.Application.Visuals import *
import System.Drawing.Color

# Get a reference to a visual on the page specified
def getVisual2(page, visualTitle):
	for vis in page.Visuals: 				
		if vis.Title == visualTitle:							
			return vis	
	return None

#Function to return a Spotfire Data Table. Will return None if the data table does not exist
#parameter: tableName - the name of the data table in the Spotfire document
def getDataTable(tableName):
	try:
		return Document.Data.Tables[tableName]
	except:
		print ("Cannot find data table: " + tableName + ". Returning None")
		return None

# A class represeting fruit - with name, diameter and colour
class Fruit:
	def __init__(self, name, diameter, colour):
		self.name = name
		self.diameter = diameter
		self.colour = colour
	def __repr__(self):
		return repr((self.name, self.diameter, self.colour))

dt = getDataTable("Fruits")

#Create cursors for the columns that will be iterated over
fruitCursor = DataValueCursor.CreateFormatted(dt.Columns["Fruit"])
diameterCursor = DataValueCursor.CreateNumeric(dt.Columns["Diameter"])
colourCursor = DataValueCursor.CreateFormatted(dt.Columns["Colour"])

# Array to hold the fruit objects
fruits = []

# Iterate over the rows in the Fruits data table
for row in dt.GetRows(fruitCursor, diameterCursor, colourCursor):
	fruit = fruitCursor.CurrentValue
	diameter = diameterCursor.CurrentValue
	colour = colourCursor.CurrentValue
	print fruit + ": " + str(diameter)
	fruits.append(Fruit(fruit, diameter, colour))

# Sort the fruit by diameter
sortedFruits = sorted(fruits, key=lambda fruit: fruit.diameter)

print sortedFruits

# Get the bar chart
barchart = getVisual2(Document.ActivePageReference, "Number in Basket per Fruit").As[BarChart]()

# Clear the color axis coloring
barchart.ColorAxis.Coloring.Clear()

# Create a .Net list to hold the fruit names in diameter order
fruitList = List[str]()

for fruit in sortedFruits:
	fruitList.Add(fruit.name)
	barchart.ColorAxis.Categorical.ColorMap[fruit.name] = System.Drawing.Color.FromName(fruit.colour);

#Set the custom sort order
dt.Columns["Fruit"].Properties.SetCustomSortOrder(fruitList)

License:  TIBCO BSD-Style License