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

    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

     converted-file.thumb.jpe.b8dc4e65348cc8e8cd6398ba4c8ceb63.jpe

    After

    converted-file.thumb.jpe.4a1a279247537e74cd04e93b4390d271.jpe

     

    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

     

     


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...