[TIBCO Spotfire] Export data table as CSV via IronPython

Here's a sample script to programmatically via IronPython export a data table as CSV

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers
from System.IO import File

writer = Document.Data.CreateDataWriter(DataWriterTypeIdentifiers.ExcelXlsDataWriter)
table = Document.ActiveDataTableReference #OR pass the DataTable as parameter
filtered = Document.ActiveFilteringSelectionReference.GetSelection(table).AsIndexSet() #OR pass the filter
stream = File.OpenWrite("PATH/NAME.csv")
names = []
for col in table.Columns:
    names.append(col.Name)
writer.Write(stream, table, filtered, names)
stream.Close()




Note: the path you pass to the File.OpenWrite function is relative to the machine running the analysis; this means that on the Web Player, unless you find a way to stream the data back to the user browser, the end user will never receive the file

[TIBCO Spotfire] Mark rows via IronPython

Here's a sample script to programmatically via IronPython mark rows with a particular value (set GRAPH as script parameter pointing to the visualization you want to work on):

from Spotfire.Dxp.Data import DataPropertyClass
from Spotfire.Dxp.Application.Visuals import VisualContent
from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import RowSelection
from Spotfire.Dxp.Data import DataValueCursor
from System import String

# get object reference
vc = GRAPH.As [VisualContent] ()
dataTable = vc.Data.DataTableReference

# get marking
marking = vc.Data.MarkingReference

rowCount = dataTable.RowCount
rowsToInclude = IndexSet (rowCount, True)
rowsToSelect = IndexSet (rowCount, False)

cursor1 = DataValueCursor.CreateFormatted (dataTable.Columns ["COLUMN_NAME"])

#Find Records by looping through all rows
idx = 0
for row in dataTable.GetRows (rowsToInclude, cursor1):
    found = False
    aTag = cursor1.CurrentValue
    print aTag
# if there’s a match, mark it
    if aTag == VALUE:
        rowsToSelect [idx] = True
        print idx
    idx = idx + 1

#Set Marking
marking.SetSelection (RowSelection (rowsToSelect), dataTable)

[TIBCO Spotfire] Filter handling via IronPython - set value

Here is a sample script to programatically via IronPython set a value for a filter in Spotfire; if the value does not exist in the allowed filter values, set it to the maximum possible

import Spotfire.Dxp.Application.Filters as filters
import Spotfire.Dxp.Application.Filters.ListBoxFilter
from Spotfire.Dxp.Application.Filters import FilterTypeIdentifiers

#use following line if the filter is to be applied to the currently active page - CAUTION, as we might alter the filter for a different filtering scheme then!
myPanel = Document.ActivePageReference.FilterPanel
# alternatively use following line to se it for a specific page
#myPanel = myPage.FilterPanel
#where myPage is a script parameter that points to the page we want it to work on
myFilter = myPanel.TableGroups[0].GetFilter("FILTER_NAME")
lbFilter = myFilter.FilterReference.As[filters.ItemFilter]()
if VALUE in lbFilter.Values:
    lbFilter.Value = VALUE
else:
    lbFilter.Value = max(lbFilter.Values)

[TIBCO Spotfire] Cumulative sum

Here's a simple formula to plot the cumulative sum (trend) of data in Spotfire

Sum([COLUMN]) OVER (intersect(AllPrevious([Axis.Rows]),[GROUP_BY_1],...,[GROUP_BY_N])) as [ALIAS]

Just put it on the X-axis and you're set. The grouping is optional

[TIBCO Spotfire] OSI PI data source template

Here is a sample connection template to add OSI PI as Data Source in the Spotfire Server:

 <!-- WARNING!!!! Old PI JDBC driver versions have .NET dependencies (RDSAWrapper.dll) - https://techsupport.osisoft.com/Troubleshooting/KB/KB00494 -->  
 <jdbc-type-settings>  
 <type-name>PI</type-name>   
 <driver>com.osisoft.jdbc.Driver</driver>   
 <!-- SQLDASServer is the OSI component we should connect to, PIServer is the name of the PI Server we want to access AS REGISTERED ON THE DAS server -->  
 <connection-url-pattern>jdbc:pisql://SQLDASServer/Data Source=PIServer;Integrated Security=SSPI</connection-url-pattern>  
 <!-- to use PI authentication instead of Windows Integrated Authentication, use this url:  
 <connection-url-pattern>jdbc:pisql://SQLDASServer/Data Source=PIServer;User ID=Username;Password=Password</connection-url-pattern>  
 -->  
 <supports-catalogs>true</supports-catalogs>  
 <supports-schemas>false</supports-schemas>  
 <supports-procedures>false</supports-procedures>  
 <use-ansii-style-outer-join>true</use-ansii-style-outer-join>  
 </jdbc-type-settings>  

NOTE: Depending on your connection needs (using Windows Integrated Authentication OR Username/Password), you need to change the connection url pattern accordingly. This means that you CANNOT leave both connection-url-pattern sections from the sample above when you add the template, BUT nonetheless, the user CAN change the connection URL to either one of those values as per his needs when he sets up the connection

Remember to read the Spotfire documentation (chapter 11.5) on how to set it up correctly!

[TIBCO Spotfire] SAP HANA data source template

Here is a sample connection template to add SAP HANA as Data Source in the Spotfire Server:

 <jdbc-type-settings>  
  <type-name>hana</type-name>  
  <driver>com.sap.db.jdbc.Driver</driver>  
  <connection-url-pattern>jdbc:sap://host:port?reconnect=true;</connection-url-pattern>  
  <ping-command>select 1 from dummy</ping-command>  
  <supports-catalogs>true</supports-catalogs>  
  <supports-schemas>true</supports-schemas>  
  <supports-procedures>true</supports-procedures>  
  <table-types>TABLE, CALC VIEW, OLAP VIEW, JOIN VIEW, HIERARCHY VIEW, VIEW</table-types>  
 </jdbc-type-settings>  

Remember to read the Spotfire documentation (chapter 11.5) on how to set it up correctly!

[TIBCO Spotfire] Excel JDBC data source template

If you want to take full advantage of the Information Designer capabilities when connecting to non-default data sources, and your analysis can't be fully developed using only Connectors and/or ADS, you might want to create your own data source template. [1]

The idea is simple: as long as you have a proper JDBC driver and can supply basic details on how to connect and handle operations, Spotfire Server allows you to add a custom connection template that end users will be able to select when creating a new Data Source. [2]

Here is a sample connection template for Excel. Note that in this case you will need the ODBC driver as well.

 <jdbc-type-settings>   
  <!-- Informative name and display-name -->   
  <type-name>ODBC-EXCEL</type-name>   
  <!-- point out the JDBC-ODBC bridge -->   
  <driver>sun.jdbc.odbc.JdbcOdbcDriver</driver>   
  <!-- Pattern displayed to administrator when setting up the Datasource -->   
  <connection-url-pattern>jdbc:odbc:excel-odbc-source</connection-url-pattern>   
  <!-- Table types allowed for EXCEL is TABLE and SYSTEM TABLE -->   
  <table-types>TABLE, SYSTEM TABLE</table-types>   
  <!-- As ping command we will use the integer constant 1. Could really be any pseudo function. -->   
  <ping-command>SELECT 1</ping-command>   
  <connection-properties />   
  <!-- Excel does not support catalogs nor schemas -->   
  <supports-catalogs>false</supports-catalogs>   
  <supports-schemas>false</supports-schemas>   
  <!-- Found an error in excel not allowing Distinct in combination with order-by on all columns. -->   
  <!-- Choice between supporting order by and distinct in favor for distinct -->   
  <!-- to make prompt without duplicates and support distinct conditioning. -->   
  <supports-order-by>false</supports-order-by>   
  <!-- Format pattern for date, time and datetime (same as timestamp). -->   
  <date-literal-format-expression>{d '$$value$$'}</date-literal-format-expression>   
  <time-literal-format-expression>{t '$$value$$'}</time-literal-format-expression>   
  <date-time-literal-format-expression>{ts '$$value$$'}</date-time-literal-format-expression>   
  </jdbc-type-settings>   

[1] Data source template documentation - Chapter 11.5
[2] Sample guidelines to add Attivio as data source

[Spotfire] Send JMS message to EMS with IronPython script

Want to take your Spotfire analysis to the next level? What about adding an extra level of interactivity to it?

Maybe you also have a BusinessWorks or BusinessEvents engine somewhere feeding data to it, and you spot something that requires your intervention, or maybe you want to replay a flow for some reason. Sound like you need to have Spotfire communicate with those engines.

HTTP? Sure it works. What if you prefer JMS instead because you also have your own EMS server?

[TIBCO Spotfire] Run script at startup from a Web Player mashup

If you created a mashup page that includes a Spotfire analysis served via Web Player using JavaScript, you can easily trigger the embedded IronPython scripts to run as soon as the report is opened.

This also works if the report is accessed directly via an URL that uses Configuration Blocks.

• Create a Document Property as a boolean and set it to false.
• Tie your script to that Document Property; every time its value changes, the script is triggered

If using the JS mashup page, have a JS script run when the onOpened event is fired and set that property. Make sure that the mashup page runs under the same site as the Web Player on the IIS server, eg: "Add application" under the Spotfire Web Player site.

Mashup page:

 <html>  
   <head>  
     <title>MySamplePage</title>  
     <script type="text/javascript" src="PATH_TO/SpotfireWeb/GetJavaScriptApi.ashx?Version=3.1"></script>  
     <script type="text/javascript" src="PATH_TO/myScript.js"></script>  
   </head>  
   <body>  
           <!-- Whatever -->  
           <div id="webPlayerDiv"/>  
   </body>  
 </html>  

Script:

 var webPlayer;  
 var webPlayerRelativePath = "PATH_TO/SpotfireWeb/";  
 var analysisPath = "PATH_TO/myAnalysis";  
   
 <!-- when the page is accessed, include the Web Player analysis via JS -->  
 window.onload = function()  
 {                 
   openWebPlayer();       
 };  
   
 var openWebPlayer = function()  
 {  
   var webPlayerCustomization = new spotfire.webPlayer.Customization();  
      <!-- enable/disable toolbar buttons -->  
   webPlayerCustomization.showCustomizableHeader = true;  
   webPlayerCustomization.showTopHeader = true;  
   webPlayerCustomization.showClose = true;  
   webPlayerCustomization.showAnalysisInfo = true;  
   webPlayerCustomization.showToolBar = true;  
   webPlayerCustomization.showExportFile = true;  
   webPlayerCustomization.showExportVisualization = true;  
   webPlayerCustomization.showUndoRedo = true;  
   webPlayerCustomization.showDodPanel = true;  
   webPlayerCustomization.showFilterPanel = true;  
   webPlayerCustomization.showPageNavigation = true;  
   webPlayerCustomization.showStatusBar = true;  
   
      webPlayer = new spotfire.webPlayer.Application(webPlayerRelativePath, webPlayerCustomization);  
   
   var onError = function(errorCode, description)  
   {  
     log('<span style="color: red;">[' + errorCode + "]: " + description + "</span>");  
   };  
   
      <!-- when the report is loaded, set our Document Property to true to trigger the script -->  
   var onOpened = function(analysisDocument)  
   {       
           webPlayer.analysisDocument.setDocumentProperty(  
                     "myProperty",  
                     "true");  
   };  
        
   webPlayer.onError(onError);  
   webPlayer.onOpened(onOpened);  
   webPlayer.open(analysisPath, "webPlayerDiv", "");  
 };  

If using the configuration block in the URL instead, add:

 &configurationBlock=PROPERTY_NAME=VALUE;  

So you'll have something like:

 http://myServer:PORT/SpotfireWeb/ViewAnalysis.aspx?file=/PATH_TO/MyAnalysis&configurationBlock=MyDocumentProperty=VALUE;  

[Spotfire] Get data from marking via script

Should you need to get data from a marking while inside an IronPython script in Spotfire, you may try:

 from Spotfire.Dxp.Data import *  
 from System import String,Object  
    
 markingName = 'some_name' #can also be passed as parameter!  
 DataTableSelection = 'some_table' #can also be passed as parameter!  
    
 #get marked rows  
 rowMask = Document.Data.Markings[markingName].GetSelection(DataTableSelection)  
    
 #get cursor on the marking for the values we want  
 cursor = DataValueCursor.CreateFormatted(DataTableSelection.Columns["some_column"])  
    
 #cycle on our cursor and do something with the values  
 for row in DataTableSelection.GetRows(rowMask.AsIndexSet(),cursor):  
         print cursor.CurrentValue  

Remember that all inputs can be passed as parameters to our script!

[TIBCO Spotfire] Link to a specific Web Player analysis page

Linking to a specific page from a Tibco Spotfire Web Player-served analysis is pretty easy.

You first need to enable the JavaScript API from the Web Player Web.config file under WEB_PLAYER_INSTALL_DIR\VERSION\webroot:

find the section and change the value to true, then restart the Web Player site from IIS.

You can now create direct links to the report pages using URLs in the format:

http(s)://web_player_url:web_player_port/SpotfireWeb/ViewAnalysis.aspx?file=/path_to_analysis&configurationBlock=SetPage(pageTitle="page_name");

where page_name is NOT case sensitive, or:

http(s)://web_player_url:web_player_port/SpotfireWeb/ViewAnalysis.aspx?file=/path_to_analysis&configurationBlock=SetPage(pageIndex="page_number");


where page_number starts from 0

Note the mandatory semicolon (;) at the end of the URL

[TIBCO Spotfire] Export only marked rows from Web Player

Users accessing Spotfire analysis from the Web Player have the ability to export data in multiple formats (assuming they were given the necessary licenses).

It is also possible to only export marked rows by selecting the data of interest, enabling the Details-On-Demand view, and exporting the data from there, simple as that.

[TIBCO Spotfire] Count grouping by columns

With custom expressions in Spotfire, you have more control on how the data is to be handled and displayed. These expressions are also useful when managing calculated values and calculated columns.

One common thing you may want to do, is the equivalent of the SQL statement:

SELECT column1, ..., columnN, COUNT(columnX) FROM table GROUP BY column1, ..., columnN

To count how many rows have a particular value in columnX, grouping the results by the unique values in column1, ..., columnN

In Spotfire, this can be accomplished with the OVER expression:

Count([columnX]) OVER ([column1], ..., [columnN])

If you need a COUNT(DISTINCT(columnX)), you can use UniqueCount([columnX]) instead.

[TIBCO Spotfire] Filter only unique values in data to display

When configuring a data source, you have multiple options to limit the data in the display visualization. One of those is Limit data using expression, which allows you to write simple expressions that will always be evaluated, regardless of the filters the user may set.

One thing it's possible to accomplish this way, is the equivalent of the SQL statement:

SELECT DISTINCT(column1, ..., columnN) FROM mytable

to only display tuples with the same values in the specified columns once, regardless of any other different values they may have in other columns.

In Spotfire, you can do this with the Rank function:

Rank(Rowid(), "Asc", [column1], ..., [columnN]) = 1

[TIBCO Spotfire] Write data to database

TIBCO Spotfire primarily reads data from a DB to populate analysis and reports, but it is also possible to have it write data back on the database.

Doing so is fairly easy; the key point is to remember that the last operation in the Information Link MUST return data back. This means that your procedure that performs the desired operation (INSERT, UPDATE, DELETE) MUST return data back, even if usually you wouldn't do that in standard SQL.