Jump to content
  • Find all columns and expressions used in graphs, tables or visuals in Spotfire

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


    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.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.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
    					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
    			## Need to check what type of map layer it is
    			for layer in mapChart.Layers:
    				## try doing it as a marker layer
    					streamWriter.WriteLine(tabString + "LayerName" + sep + re.sub(regExp, '', layer.Name) + "rn")
    					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
    					if pieMarker.SectorSizeAxis.Expression<>"":
    						streamWriter.WriteLine(tabString + "SectorSizeAxis" + sep + re.sub(regExp, '', mLayer.SectorSizeAxis.Expression) + "rn")
    					## Now try as a feature layer
    						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")			
    						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
    ## 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)


    • 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



    User Feedback

    Recommended Comments

    There are no comments to display.

  • Create New...