Find all columns and expressions used in graphs, tables or visuals in Spotfire

Introduction

A common task to do is to refine what data you have in Spotfire, or to understand what parts of your data is actually being used in Spotfire. This is particularly true when you have many charts, tables and tabs in Spotfire. By understanding what columns are being used you are then able to edit or remove columns without risking breaking any of your existing visuals. This wiki article shows a script that can be used to go through each tab in your tool and create a table with details on the columns and expressions used in any visuals. It is not exhaustive in that not all visuals are covered or parameters of each visual but the script provides a template that can be altered to suit your needs.

Script and Output Summary

The script contained within this wiki article uses a few common techniques to extract the visual column and expression usage. It uses a memory stream to create a new data table in Spotfire. Then it loops through all pages and visuals and creates adds an entry into this memory stream. By adding a new line and carriage return at the end of each addition to this stream, we are effectively creating a new row of data. This script adds each property examined as a new row in the table. The table is defined as:

• Tab - the name of the tab the visual is on
• Type - currently will always be visual (an idea for future enhancement will be to cover other aspects of your tool such as filters)
• Name - the title of the visual
• Sub Type - this visual type (as described in Spotfire's C# API)
• Property - the property of the visual being examined i.e. x-axis, color axis etc.
• Value - the value extracted for the property i.e. a column name, or a column expression

Column and Expression Usage Script

The script below is an IronPython script. For details on IronPython and how to use it in Spotfire please read this wiki article. This script will create a new data table in your Spotfire tool called Tab Summary. You can alter the script to change this table name if desired. Each time the script is run, this table will be overwritten. 

# Copyright (c) 2017-2019 TIBCO Software Inc. All Rights Reserved.

import re
from System.Text import *
from Spotfire.Dxp.Application.Visuals import *
from Spotfire.Dxp.Data import DataPropertyClass,DataProperty,DataType
from Spotfire.Dxp.Application.Visuals.Maps import *
from Spotfire.Dxp.Application.Visuals import HtmlTextArea
from System.Collections.Generic import HashSet
from System.IO import FileStream, FileMode, File, MemoryStream, SeekOrigin, StreamWriter
import System.String
from Spotfire.Dxp.Data.Import import TextDataReaderSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource
from Spotfire.Dxp.Data.Import import DataTableDataSource
from System import DateTime

## Function to create table that will hold the results
def LoadNewTable(dataTableName, stream):
    settings = TextDataReaderSettings()
    settings.Separator = sep
    settings.AddColumnNameRow(0)
    settings.ClearDataTypes(False)
    settings.SetDataType(0, DataType.String)
    settings.SetDataType(1, DataType.String)
    settings.SetDataType(2, DataType.String)
    settings.SetDataType(3, DataType.String)
    settings.SetDataType(4, DataType.String)
    settings.SetDataType(5, DataType.String)
    settings.SetDataType(6, DataType.String)

    stream.Seek(0, SeekOrigin.Begin)
    fs = TextFileDataSource(stream, settings)
    if Document.Data.Tables.Contains(dataTableName):
        Document.Data.Tables[dataTableName].ReplaceData(fs)
    else:
        Document.Data.Tables.Add(dataTableName, fs)

## function to extract trellis settings of any visual
def GetTrellisProps(vc, tabString, streamWriter):
	if vc.Trellis.ColumnAxis.Expression <> "":
		streamWriter.WriteLine(tabString + "Trellis.ColumnAxis" + sep + re.sub(regExp, '', vc.Trellis.ColumnAxis.Expression) + "rn")
	elif vc.Trellis.RowAxis.Expression <> "":
		streamWriter.WriteLine(tabString + "Trellis.RowAxis" + sep + re.sub(regExp, '', vc.Trellis.RowAxis.Expression) + "rn")
	elif vc.Trellis.PanelAxis.Expression <> "":
		streamWriter.WriteLine(tabString + "Trellis.PanelAxis" + sep + re.sub(regExp, '', vc.Trellis.PanelAxis.Expression) + "rn") 

# Set separator
sep = "|"
## regexp for expressions
regExp = '[<>nr]+'

# Create new memory stream to hold results
stream = MemoryStream()
streamWriter = StreamWriter(stream)#, Encoding.UTF8)
streamWriter.WriteLine("Tab|Type|Name|Sub Type|Property|Valuern")

## Loop pages / tabs in tool and extract visual properties
for page in Application.Document.Pages:
	for vis in page.Visuals:

		## start adding data from tab
		vc = vis.As[VisualContent]()
		tabString = page.Title + sep + "Visual" + sep + vc.Title + sep

		## Get table settings
		if vis.TypeId == VisualTypeIdentifiers.Table:
			tabString = tabString + str(vis.TypeId).replace("TypeIdentifier:Spotfire.","") + sep
			tablePlot = vis.As[TablePlot]()	
			cols = ""
			for col in tablePlot.TableColumns:
				if cols == "":
					cols = col.Name
				else:
					cols = cols + ", " + col.Name

			streamWriter.WriteLine(tabString + "Columns" + sep + cols + "rn")

		## if visual is bar, line or scatter
		if vis.TypeId == VisualTypeIdentifiers.BarChart or vis.TypeId == VisualTypeIdentifiers.LineChart or vis.TypeId == VisualTypeIdentifiers.ScatterPlot:
			tabString = tabString + str(vis.TypeId).replace("TypeIdentifier:Spotfire.","") + sep

			## Grab axis settings
			if vc.XAxis.Expression <> "":
				streamWriter.WriteLine(tabString + "XAxis" + sep + re.sub(regExp, '', vc.XAxis.Expression) + "rn")
			if vc.YAxis.Expression <> "":
				streamWriter.WriteLine(tabString + "YAxis" + sep + re.sub(regExp, '', vc.YAxis.Expression) + "rn")
			if vc.ColorAxis.Expression <> "":
				streamWriter.WriteLine(tabString + "ColorAxis" + sep + re.sub(regExp, '', vc.ColorAxis.Expression) + "rn")

			## Do trellis options
			GetTrellisProps(vc, tabString, streamWriter)

		## if visual is a map chart
		if vis.TypeId == VisualTypeIdentifiers.MapChart2:
			tabString = tabString + str(vis.TypeId).replace("TypeIdentifier:Spotfire.","") + sep
			mapChart=vis.As[MapChart]()

			## Need to check what type of map layer it is
			for layer in mapChart.Layers:
				## try doing it as a marker layer
				try:
					streamWriter.WriteLine(tabString + "LayerName" + sep + re.sub(regExp, '', layer.Name) + "rn")
					mLayer=layer.As[MarkerLayerVisualization]()
					
					if str(mLayer.SizeAxis.Expression) <> "":
						streamWriter.WriteLine(tabString + "SizeAxis" + sep + re.sub(regExp, '', mLayer.SizeAxis.Expression) + "rn")
					if str(mLayer.ColorAxis.Expression) <> "":
						streamWriter.WriteLine(tabString + "ColorAxis" + sep + re.sub(regExp, '', mLayer.ColorAxis.Expression) + "rn")
					## check if pies are used
					pieMarker=mLayer.PieMarker
					if pieMarker.SectorSizeAxis.Expression<>"":
						streamWriter.WriteLine(tabString + "SectorSizeAxis" + sep + re.sub(regExp, '', mLayer.SectorSizeAxis.Expression) + "rn")
				except:
					## Now try as a feature layer
					try:
						mLayer=layer.As[FeatureLayerVisualization]()
						streamWriter.WriteLine(tabString + "FeatureTableReference" + sep + re.sub(regExp, '', mLayer.FeatureTableReference.Name) + "rn")
						if re.sub('[<>]+', '', str(mLayer.ColorAxis.Expression))<>"":
							streamWriter.WriteLine(tabString + "ColorAxis" + sep + re.sub(regExp, '', mLayer.ColorAxis.Expression) + "rn")			
					except:
						streamWriter.WriteLine(tabString + "LayerName" + sep + re.sub(regExp, '', layer.Title) + "rn")
	
		## if the visual is a KPI chart
		if vis.TypeId == VisualTypeIdentifiers.KpiChart:
			kpichart = vis.As[KpiChart]()
			tabString = tabString + str(vis.TypeId).replace("TypeIdentifier:Spotfire.","") + sep

			## KPIs can have multiple sets of kpis collections so need to loop these
			for kpiContent in kpichart.KpiCollection:
				kpiVisualization = kpiContent.Visualization
				streamWriter.WriteLine(tabString + "Name" + sep + kpiVisualization.Title + "rn")
				if str(kpiVisualization.YAxis.Expression) <>"":
					streamWriter.WriteLine(tabString + "YAxis" + sep + re.sub(regExp, '', kpiVisualization.YAxis.Expression) + "rn")
				if str(kpiVisualization.XAxis.Expression) <>"":
					streamWriter.WriteLine(tabString + "XAxis" + sep + re.sub(regExp, '', kpiVisualization.XAxis.Expression) + "rn")
				if str(kpiVisualization.ComparativeAxis.Expression) <>"":
					streamWriter.WriteLine(tabString + "ComparativeAxis" + sep + re.sub(regExp, '', kpiVisualization.ComparativeAxis.Expression) + "rn")	

		## if visual is a cross table
		if vis.TypeId == VisualTypeIdentifiers.CrossTable:
			vc = vis.As[VisualContent]()
			tabString = tabString + str(vis.TypeId).replace("TypeIdentifier:Spotfire.","") + sep
			if vc.ColumnAxis.Expression <> "":
				streamWriter.WriteLine(tabString + "ColumnAxis" + sep + re.sub(regExp, '', vc.ColumnAxis.Expression) + "rn")
			if vc.RowAxis.Expression <> "":
				streamWriter.WriteLine(tabString + "RowAxis" + sep + re.sub(regExp, '', vc.RowAxis.Expression) + "rn")
			if vc.MeasureAxis.Expression <> "":
				streamWriter.WriteLine(tabString + "MeasureAxis" + sep + re.sub(regExp, '', vc.MeasureAxis.Expression) + "rn")

		## if the visual is a treemap
		if vis.TypeId == VisualTypeIdentifiers.Treemap:
			vc = vis.As[VisualContent]()
			tabString = tabString + str(vis.TypeId).replace("TypeIdentifier:Spotfire.","") + sep
			streamWriter.WriteLine(tabString + "HierarchyAxis" + sep + re.sub(regExp, '', vc.HierarchyAxis.Expression) + "rn")
			streamWriter.WriteLine(tabString + "ColorAxis" + sep + re.sub(regExp, '', vc.ColorAxis.Expression) + "rn")
			streamWriter.WriteLine(tabString + "SizeAxis" + sep + re.sub(regExp, '', vc.SizeAxis.Expression) + "rn")

## flush the stream as we are finished
streamWriter.Flush()
## call function to write stream to a data table - you can alter the name below or this code could be changed to allow it being passed in as a document property for example
LoadNewTable("Tab Summary", stream)

Limitations

• Not all chart types are covered in this script. For instance parallel plots and box plots are not covered
• The trellis properties are only checked for scatter, line and bar charts. However, using the function defined in the script it should be possible to apply this to map charts and others for example
• Not all properties are extracted. Currently axis settings and colour settings are for most chart types. It would be possible to extent this script to cover any other column usgae such as size, shape etc.

License:  TIBCO BSD-Style License