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

Data Table to JSON

Sometimes you want to serialize the data table into a JSON string so you can feed javascript widgets that require such format. In this example, we get the filtered rows from a given data table and generate it's JSON

Consider the following input table
ABC
A1B1C1
A2B2C2

The output should look like this after running the script:

[{'A':'A1','B':'B1','C':'C1'},{'A':'A2','B':'B2','C':'C2'},{'A':'A3','B':'B3','C':'C3'}]


#get filtered rows
#dataTable can be a Script parameter pointing to a specific Data Table. In this case, we use the current one
dataTable = Document.ActiveDataTableReference

#get rows and columns from our selection
rows = Document.ActiveFilteringSelectionReference.GetSelection(dataTable).AsIndexSet()
cols = dataTable.Columns

#generate our "json" data
data=[]
for r in rows:
 item={}
 for c in cols:
  item[c.Name] = c.RowValues.GetFormattedValue(r)
 data.append(item)

#convert json to string
import clr
clr.AddReference('System.Web.Extensions')
from System.Web.Script.Serialization import JavaScriptSerializer
json=JavaScriptSerializer().Serialize(data)

#save to document property to use later in javascript
Document.Properties["json"]=json

Note:  If you want to limit the number of columns in your output, you can use the FindAll method. Suppose you have many columns that contains the word Project (Project Name, Project Date, Big Project, Small Project and you also want to include the Budget column, then you can use the Spotfire Search Syntax

for c in cols.FindAll("*Project* OR Budget"):
  print c

No comments: