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
Recommended Comments
There are no comments to display.