IronPython script to Calculate Cross Table Sums

Introduction

This script allows users to calculate the grand total of the visible rows in a cross table. The calculated sums are presented in a new Spotfire data table. This is useful when you have used complex custom expressions in a cross table and the grand totals are not as expected.

Calculate Cross Table Sums

The below script writes a cross table to memory in a tab-delimited format, reads it back in and calculated the sum for each of the columns. Finally, it outputs the sum to a new data table. 

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

# Calculate totals for CrossTable
#	Andrew Berridge, TIBCO Software, October 2014
#
# Parameters:
#	crossTable - the cross table for which to calculate the total
#	totalsDataTableName - the name of the data table to save the totals in

from Spotfire.Dxp.Application.Visuals import VisualContent
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


stream = MemoryStream()
writer = StreamWriter(stream)
crossTable.As[VisualContent]().ExportText(writer)

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

totalsStream = MemoryStream()
totalsWriter = StreamWriter(totalsStream)

# set up the text data reader
readerSettings = TextDataReaderSettings()
readerSettings.Separator = "t"
readerSettings.AddColumnNameRow(0)

line = ""
bFirstLine = True
values = Dictionary[int, float]()
while (True):
	line = reader.ReadLine()
	if line == None: break
	print line
	lineToWrite = ""
	intCol = 0
	for value in line.Split("t"):
		if bFirstLine:
			if value.Contains("Total"):
				#Store 0 and the column index in the dictionary
				values.Add(intCol, 0)
				readerSettings.SetDataType(intCol, DataType.Real)
				if lineToWrite != "": lineToWrite += "t"
				lineToWrite += value
			pass
		else:
			if values.ContainsKey(intCol):
				print "value " + str(value)
				values[intCol] += float(value)
			
		intCol += 1

	if bFirstLine:
		totalsWriter.Write(lineToWrite + "rn")	
	bFirstLine = False

bFirstTimeThrough = True
lineToWrite = ""
for value in values.Keys:
	if not bFirstTimeThrough:
		lineToWrite += "t"
	print value
	lineToWrite += str(values[value])
	bFirstTimeThrough = False

print lineToWrite
totalsWriter.Write(lineToWrite + "rn")


totalsWriter.Flush()
totalsStream.Seek(0, SeekOrigin.Begin)

# create a data source to read in the stream
textDataSource = TextFileDataSource(totalsStream, readerSettings)

# add the data into a Data Table in Spotfire
if Document.Data.Tables.Contains(totalsDataTableName):
     Document.Data.Tables[totalsDataTableName].ReplaceData(textDataSource)
else:
     newTable = Document.Data.Tables.Add(totalsDataTableName, textDataSource)
     tableSettings = DataTableSaveSettings (newTable, False, False)
     Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)

License:  TIBCO BSD-Style License