[VBA] Excel open popup from macro

As usual when dealing with business users, you'll find yourself tinkering with Excel files which could include macros.

As usual when business users try their hands at this newfangled tech stuff, they will end up putting in a minuscule yet showstopping mistake you need to spot.

Although the VBA debugger is actually pretty good, sometimes the error is just stemming from a wrong value being inserted in a cell. In this case, it's just faster to simply display the value(s) in a popup window and then searhing for the offending cell in the spreadsheet.

The MsgBox function alongside with the CStr one will become your new best friends here:

MsgBox ("I am putting this value " & CStr(your_value) & " but maybe it is wrong")

Where & is used to concatenate strings and your_value can be a variable or an expression within the macro you're debugging.

[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!