test

Warning when upgrading to Spotfire 10

TIBCO has deprecated the JQueryUI Javascript library in the latest Spotfire 10.0 release

TIBCO Community pages show us how to use JQuery properly

Cross Table to HTML

Here is an example of how to extract the data from a Cross Table visualization to format the way you want it with HTML. The downside is that the output is not dynamic or interactive when filtering data, so and the script needs to be run to refresh the data.



# The way this scrip works is
# 1 Export Cross Table data into a temp file
# 2 Read temp file and create html
# 3 Write html into a Text Area
# Note: viz and textArea are script parameters

from System.IO import Path, StreamWriter
from Spotfire.Dxp.Application.Visuals import CrossTablePlot

#1 Export Cross Table data into a temp file
#1.a Temp file for storing the cross table data
tempFolder = Path.GetTempPath()
tempFilename = Path.GetTempFileName()

#1.b Export CrossTable data to the temp file
writer = StreamWriter(tempFilename)
viz.As[CrossTablePlot]().ExportText(writer)

#2 Read temp file and create html
#2.a open the temp file    
f = open(tempFilename)

#2.b Build the HTML
htmlReport = " <TABLE style='border:1px solid #C0C0C0;' cellpadding='2' cellspacing='0'>\n"
htmlReport += "<TR style='background-color: #C0C0C0;border:1px solid #C0C0C0;'>"
htmlReport += "<td style='font-weight:bold;'>2014 YTD Key Metrics</td><td colspan='3' style='width:175px;text-align:center;font-weight:bold;'>Recordable Injuries</td><td colspan='3' style='width:175px;text-align:center;font-weight:bold;'>High Potential(Hi-Po)</td><td colspan='2' style='width:175px;text-align:center;font-weight:bold;'>LOPC</td><td colspan='2' style='width:175px;text-align:center;font-weight:bold;'>Process Safety Events</td>"
htmlReport += "</TR>\n"

htmlReport += "<tr style='background-color: #D9D9D9;'>"
htmlReport += "<td></td><td style='font-weight:bold;text-align:center;'>Week</td><td style='font-weight:bold;text-align:center;'>MTD</td><td style='font-weight:bold;text-align:center;'>YTD</td><td style='font-weight:bold;text-align:center;'>Week</td><td style='font-weight:bold;text-align:center;'>MTD</td><td style='font-weight:bold;text-align:center;'>YTD</td><td style='font-weight:bold;text-align:center;'>Volume</td><td style='font-weight:bold;text-align:center;'>Count</td><td style='font-weight:bold;'>T1</td><td style='font-weight:bold;text-align:center;'>T2</td>"
htmlReport += "</TR>\n"

#rowCount variable is used to eliminate the first row from crosstable.  
rowCount=0
#firstColumn variable is used to eliminate the first column from crosstable.
firstColumn=""
#To identify the Subtotal row. 
isSubTotalRow=False

#reading line by line from the temp table
for line in f:
rowCount=rowCount+1
if rowCount>1:
htmlReport += "<TR>"
count=1
for col in line.split('\t'):
if count == 1:
if firstColumn != col:
htmlReport += "<tr style='background-color: #D9D9D9;'>"
htmlReport += "<td colspan='11' style='font-weight:bold;text-decoration:underline;border:1px solid #C0C0C0;'>" + col + ":"+"</td>"
htmlReport += "</TR>\n"
firstColumn=col
if count ==2:
#Highlight the background color for Sub total row.
if col == "Subtotal":
col = "Total " + firstColumn
isSubTotalRow=True
htmlReport += "<TD style='background-color: #C0C0C0;border:1px solid black;'>" +col+"</TD>"
else:
htmlReport += "<TD style='border:1px solid #C0C0C0;'>&nbsp;&nbsp;&nbsp;" +col+"</TD>"
elif count >2:
if col=="0":
col="-"
if isSubTotalRow==True:
htmlReport +="<TD style='background-color: #C0C0C0;border:1px solid black;'>"+col+"</TD>"
else:
htmlReport += "<TD style='border:1px solid #C0C0C0;'>"+col+"</TD>"
count=count+1
isSubTotalRow=False
htmlReport += "</TR>\n"
f.close()
htmlReport += "</TABLE>\n"

#3 Write html into a Text Area
from Spotfire.Dxp.Application.Visuals import HtmlTextArea
textArea.As[HtmlTextArea]().HtmlContent = htmlReport

1 comment:

rohit bhatia said...

Hello,
I am trying to something similar for my current project.I had 2 questions.

1) Can we add "sum" to the "Total" rows (i.e.Onshore, offshore) for each of the columns. If yes, could you help me with the sample code

2) The other requirement with this kind of a cross table is, to minimize and maximize the content inside each "Total" row.
say for example, if I click on "Offshore" all the rows inside Offshore should be displayed(maximized), else it should be minimized. Could this is possible?