IronPython script to Get Aggregated Data from Spotfire

Introduction

It is sometimes necessary to access aggregated data from within an IronPython script. This can be done by creating a temporary cross table and then exporting that cross table in memory. 

Create Temp Cross Table

The below script can be used to create a new cross table visualization, set the data table and axes of the visualization and export the data in the cross table. Finally, it will delete the temporary cross table (by deleting its containing page) after use.

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

from Spotfire.Dxp.Application.Visuals import *
import System.Drawing.Bitmap
from System.IO import StringReader, StreamReader, StreamWriter, MemoryStream, SeekOrigin
from System.Collections.Generic import Dictionary
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings


def getPage(pageTitle):
	for page in Document.Pages:
		if page.Title == pageTitle:
			return page

def getVisual(page, visTitle):
	for vis in page.Visuals:
		if vis.Title == visTitle:
			return vis.As[VisualContent]()
			
			

# Create a temporary cross table
tempPage = Document.Pages.AddNew("Temp");

crossTablePlot = tempPage.Visuals.AddNew(VisualTypeIdentifiers.CrossTable).As[CrossTablePlot]();

crossTablePlot.Data.DataTableReference = Document.Data.Tables["Beds.and.Homeless"];
crossTablePlot.MeasureAxis.Expression = "Sum([Beds]) as [Beds]";
crossTablePlot.RowAxis.Expression = "<[State]>";
crossTablePlot.ColumnAxis.Expression = "<[Axis.Default.Names]>";
crossTablePlot.ShowColumnGrandTotal = False;
crossTablePlot.ShowRowGrandTotal = False;
crossTablePlot.Data.WhereClauseExpression = "[State] = "AK""

# Read the cross table
stream = MemoryStream()
writer = StreamWriter(stream)
crossTablePlot.ExportText(writer)

writer.Flush()
stream.Seek(0, SeekOrigin.Begin)
reader = StreamReader(stream)

line = ""
bFirstLine = True
headers = Dictionary[str, int]()
while (True):
	line = reader.ReadLine()
	if line == None: break
	if bFirstLine:
		colIndex = 0
		for col in line.Split('t'):
			headers.Add(col, colIndex)
			colIndex += 1
		bFirstLine = False;
	else:
		values = line.Split('t')
		print values[headers["State"]] + " " + str(values[headers["Beds"]])

# Delete the page
for page in Document.Pages:
	if page == tempPage:
		pass
		# Document.Pages.Remove(page)

License:  TIBCO BSD-Style License