Reverse sort order on filters

Unfortunately there is no way to reverse the sort order on non categorical data such as dates and numbers on columns or filters. A work around is to create a calculated column to rank and concatenate.

Repeat("0",Max(Len(String(DenseRank([Date],"desc")))) - Len(String(DenseRank([Date],"desc")))) & DenseRank([Date],"desc") & " - " & [Date] as [DDate]






Highlight selected spotfire action control buttons






html
<style>

.on{
  background-color: #ABFF00;
  box-shadow:   rgba(45, 163, 215, 0.5) 0 0px 0px 0px, 
           inset rgb(45,163, 215) 0 -2px 0px, 
                 rgb(45,163, 215) 0 0px 7px;
}

.container {
    display: flex;
    flex-flow: row wrap;
    justify-content: space-around;
    backgrond:yellow;
}
.container > div {
    margin: 0px;
    padding: 0px;
    xwidth:220px;
    xheight:50px;
    xbackground-color: rgb(14,145,172);
    xborder-radius:5px;
    text-align:center;
    xcolor:white;
}

.r3 {
 font-weight:bold;
 width:100%;
 border-bottom:1px solid black;
 margin-right:20px; 
 text-align:center 
}

</style>


<div class="container">

   <div>
 <div class='r3'> Last <SpotfireControl id="action_button_control_here" /> days</div>
<span id='dayBtns'>
 <SpotfireControl id="action_button_control_here" /> <SpotfireControl id="action_button_control_here" />  <SpotfireControl id="action_button_control_here" /></span>
   </div>
   <div>
   <div class='r3'> Options</div>
    <TABLE border=0  width="1px" >
      <TBODY>
 <TR id='helpBtns'>
  <TD title="Shows subtitle (help) for each visualization">Help</TD>
  <TD ><SpotfireControl id="action_button_control_here" /></TD>
  <td ><SpotfireControl id="action_button_control_here" /></TD>
 </TR>
 <TR id='filterBtns'>
  <TD title="Shows or hides the Details on Demand Panel">Filters</TD>
  <TD ><SpotfireControl id="action_button_control_here" /></TD>
  <td ><SpotfireControl id="action_button_control_here" /></TD>
 </TR>
 <TR id='detailsBtns'>
  <TD title="Shows or hides the Details on Demand Panel">Details</TD>
  <TD ><SpotfireControl id="action_button_control_here" /></TD>
  <td ><SpotfireControl id="88ed6f5873ad4fb0b4cb6542696f4e3e" /></TD>
 </TR>
      </tbody>
    </table>
   </div>
</div>



JS
function lightsOff(cssSel){$(cssSel).removeClass("on")}


//make day selection buttons highlight when clicked
$("#dayBtns input").click(function(){
    $("#dayBtns input").removeClass("on")
 $(this).addClass("on")
})

//make on/off help buttons highlightable
$("#helpBtns input").click(function(){
    $("#helpBtns input").removeClass("on")
 $(this).addClass("on")
})

//add magic to details on|off buttons 
$("#detailsBtns input").click(function(){
    $("#detailsBtns input").removeClass("on")
 $(this).addClass("on")
})

//make spotfire action control buttons to turn on|off filter panel to highlight
$("#filterBtns input").click(function(){
    $("#filterBtns input").removeClass("on")
 $(this).addClass("on")
})


//highlight default buttons 
$("#dayBtns input:first").addClass("on")
$("#helpBtns input:last").addClass("on")
$("#detailsBtns input:last").addClass("on")

$("#filterBtns input:last").addClass("on")


Delete first 10 rows

#dt is a data table script parameter
from Spotfire.Dxp.Data import RowSelection, IndexSet

#select all rows
selection   = IndexSet(dt.RowCount,True)

#mark first 10 rows row as true and the rest false
for r in selection:
   selection[r] = (r<=10)

#delete rows based on selection (rows from indexset marked as true)
dt.RemoveRows(RowSelection(selection))

Gauge.js in Spotfire

Gauge.js implementation in Spotfire



Ingredients:
  • 1 text area
  • calculated value control or label property control
  • two javascripts: gauge.js and yourGauge.js in textarea

Example 1





html
<canvas id="gauge1"></canvas>
<span id="calcValue1" style='display:none'>
   <SpotfireControl id="SpotfireCalculatedValueControlHere" />
</span>   

note: the spotire calculated value must not contain any formatting

gauge.js
copy and paste the javascript contents of gauge.js from this link:
http://bernii.github.io/gauge.js/dist/gauge.min.js 


Note: If using more than one gauge in a tab, only one gauge.js is needed in one text area.

yourGauge.js
var opts = {
  lines: 12,
  angle: 0.054,
  lineWidth: 0.54,
  pointer: {
    length: 0.8,
    strokeWidth: 0.035,
    color: '#000000'
  },
  staticZones: [
   {strokeStyle: "#F03E3E", min: 0, max: 130}, // Red from 0 to 130
   {strokeStyle: "#FFDD00", min: 130, max: 250}, // Yellow
   {strokeStyle: "#30B32D", min: 250, max: 320}, // Green
   {strokeStyle: "#FFDD00", min: 320, max: 460}, // Yellow
   {strokeStyle: "#F03E3E", min: 460, max: 1000}  // Red
],
staticLabels: {
  font: "10px sans-serif",  
  labels: [000, 250, 350, 450, 550, 1000],  
  color: "#000000",  
  fractionDigits: 0  
},
  limitMax: 'false', 
  percentColors: [[0.0, "#a9d70b" ], [0.50, "#f9c802"], [1.0, "#ff0000"]], 
  strokeColor: '#E0E0E0',
  generateGradient: true
};

var target = document.getElementById('gauge1');
var gauge = new Gauge(target).setOptions(opts);
gauge.maxValue = 1000;
gauge.animationSpeed = 12;
//gauge.set(250);

function refreshGauge1(){
  val = parseInt($("#calcValue1").text())
  if(isNaN(val)) return

  gauge.set(val)
}

$("#calcValue1").one('DOMSubtreeModified',refreshGauge1)

refreshGauge1();

To show the actual calculated value with specific formatting, add another calculated value wrapped in a div with proper positioning:


191 lb

html
<canvas id="gauge1"></canvas>
<span id="calcValue1" style='display:none'>
   <SpotfireControl id="SpotfireCalculatedValueControlHere" />
</span>   

<div style='position:absolute;top:150px;left:125px;font-size:30px'>
   <SpotfireControl id="VisibleSpotfireCalculatedValueControlHere" /></div>


note: The visible Spotfire calculated value can contain any formatting, but it will not animate the same was as the next example
Example 2



html
<div style="text-align:center">
   <canvas width="200" height="170" id="gauge2"></canvas>
<div id="measure2
   style="font-size:45px;
        position:absolute;
        top:55px;left:0;right:0;
        text-align:center;">
   </div>
</div>

<span id="calcValue2" style='display:none'>
   <SpotfireControl id="SpotfireCalculatedValueControlHere" 
</span>    

js
var opts = {
  lines: 12, 
  angle: 0.22,
  lineWidth: 0.1, 
  pointer: {
    length: 0.5, 
    strokeWidth: 0.035, 
    color: '#afafaf' 
  },
  limitMax: 'false', 
  colorStart: '#2DA3DC', 
  colorStop: '#C0C0DB',
  strokeColor: '#EEEEEE', 
  generateGradient: true
};
var target = document.getElementById('gauge2'); 
var gauge = new Donut(target).setOptions(opts); 

gauge.maxValue = 100; 
gauge.animationSpeed = 32; 
gauge.setTextField(document.getElementById("measure2"));

function refreshGauge2(){
  val = parseInt($("#calcValue2").text())
  if(isNaN(val)) return

  gauge.set(val)
}

$("#calcValue2").one('DOMSubtreeModified',refreshGauge2)

refreshGauge2();




Note: When editing the html or javascript from the text area, the gauge might seem to disappear. Just click another tab and come back to re-render the gauge. It also works by making the calc value change.



Current user in webplayer

The System.Environment shows the actual logged in user in client but not in webplayer.  A work around is to use bookmarks

from Spotfire.Dxp.Application import BookmarkComponentFlags  
from Spotfire.Dxp.Application.AnalyticItems import BookmarkManager

#define name of temp bookmark (make sure is unique)
bookmarkName = "$_tmp_bookmark_$"

#create the bookmark. Second argument specifies what to capture
myBookmark = Document.Bookmarks.AddNew(bookmarkName ,BookmarkComponentFlags.FilterSettings)

#extract data from myBookmark 
print myBookmark.CreatedBy

#delete temp bookmark
Document.Bookmarks.Remove(myBookmark)


GetDistinctRows (7.0+)

#get unique values from column (7.0)
from Spotfire.Dxp.Data import DataValueCursor
from Spotfire.Dxp.Data import IndexSet

tableName = 'MyDataTable'
columnName = 'MycolumnName'

dt = Document.Data.Tables[tableName]
cursor = DataValueCursor.Create[str](dt.Columns[columnName])
distinctRows = dt.GetDistinctRows(None,cursor) #none param to grab them by the all
#distinctRows = dt.GetDistinctRows(IndexSet(dt.RowCount,True),cursor) #...or an index set with all true to get them all.

#get uniques
vals = []
distinctRows.Reset()
while distinctRows.MoveNext():vals.append(cursor.CurrentValue)

#show results
print vals

Trigger script when clicking on a tab

1. Page is opened
2. Text Area renders and executes Javascript
3. Javascript finds hidden action control link and clicks it

html
<div id='hiddenActionControl' style='display:none'>
<SpotfireControl id="link4ct10nc0n7r01" />
</div>

js
//executes every time the text area renders
$('#hiddenActionControl a').click()

script
#this script is triggered by the javascript associated in the text area through a hidden action control link.
Document.Properties["tabCount"] = Document.Properties["tabCount"] + 1

popup form

If you want to hack yourself and make the analysis more complicated that what it needs to, then you can create a windows form using clr but this form will only be available on the thick client (see here)

For webplayer AND client to work, you can use javascript and jquery dialogs. Unfortunateley jquery dialogs can't hold spotfire controls so far.

html
<button id='btnForm'>Submit feedback</button>
<pre id="myForm" >

Name        <input/>

Experience  <select>
     <option>Great</option>
     <option>Average</option>
     <option>Poor</option>
   </select>

Comments    <textarea/>

</pre>

js
dlg = $( "#myForm" ).dialog({
      autoOpen: false,
      height: 400,
      width: 350,
      modal: true,
      buttons: {
        "Submit comments": function() {
alert("Thanks for your feedback!")
dlg.dialog( "close" );
        },
        Cancel: function() {
          dlg.dialog( "close" );
        }
      }
});

$("#btnForm").button().on('click',function(){
dlg.dialog( "open" )
})




Selecting a column in Custom Expressions

Double click on a column name square bracket to select the entire column when editing Custom Expressions


Change bar segment labels from percentage to value

# barChart is a BarChart Visualizaiton script parameter
# labelInformationType is an integer document property script parameter with values 0|1 for pct or value
from Spotfire.Dxp.Application.Visuals import BarChart
barChart.As[BarChart]().SegmentLabelInformationType = labelInformationType 


# if you need to toggle from percantage to value, you can do this too:
from Spotfire.Dxp.Application.Visuals import BarChart, BarChart,LabelInformationType
barChart = barChart.As[BarChart]()
barChart.SegmentLabelInformationType = LabelInformationType.Value if barChart.SegmentLabelInformationType==LabelInformationType.Percentage else LabelInformationType.Percentage

Web Service to Data Table

#This script calls a webservice, parses JSON and dumps data to a datatable.

#GETS JSON DATA FROM URL
def getData(url):
import clr
clr.AddReference('System.Web.Extensions')
from System.Net import WebClient
from System.Web.Script.Serialization import JavaScriptSerializer
from System.IO import StreamReader

#Get the json data from web service
wc = WebClient()
wc.Headers.Add('Content-Type', 'application/x-www-form-urlencoded')
requestData= wc.OpenRead(url)
reader = StreamReader (requestData);
response = reader.ReadToEnd()

#Parse the results
js = JavaScriptSerializer()
responseDict = js.Deserialize(response,object)
return responseDict

#Convert JSON to tab-delimited text format
def Dict2TXT(dict, delimiter="\t"):
# Get first row to get headers
data=dict
tmp=[]
for x in data[0]: tmp.append(x.Key)
textData = "\t".join(tmp) + "\r\n"

#Get the rest of the data
for x in data:
tmp=[]
for y in x: tmp.append(y.Value)
textData += "\t".join(tmp) + "\r\n"

return textData

#Read tab-delimited text format in Spotfire
def txt2table(textData, dataTableName="Web Service Data",delimiter="\t"):
from System.IO import StreamWriter, MemoryStream, SeekOrigin
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings

#Make a stream from the string
stream = MemoryStream()
writer = StreamWriter(stream)
writer.Write(textData)
writer.Flush()
stream.Seek(0, SeekOrigin.Begin)

#Set up the text data reader
readerSettings = TextDataReaderSettings()
readerSettings.Separator = delimiter
readerSettings.AddColumnNameRow(0)

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

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


#GETS JSON DATA FROM URL
data = getData("http://services.groupkt.com/country/get/all")
dict = data["RestResponse"]["result"]

#Convert JSON to tab-delimited text format
txt = Dict2TXT(dict)

#Read tab-delimited text format in Spotfire
txt2table(txt,"Countries")

Marking programatically



This script came from here, which include the bones. Here is the meat:
## we need the from/import statements so Spotfire understands the code
from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import RowSelection
from Spotfire.Dxp.Data import DataValueCursor
from Spotfire.Dxp.Data import DataSelection
## get count of rows in data table
rowCount = dataTable.RowCount
## create index sets required to loop over rows
rowsToInclude = IndexSet(rowCount,True)
## this index set we will populate with rows to mark - the False specifies it is not populated
rowsToMark = IndexSet(rowCount,False)
## create a cursor so we can refer to the column of interest
columnCursor = DataValueCursor.CreateFormatted(dataTable.Columns["Value"])
## check if our document property was set to None or not
if dropDownValue != "":
    ## Now loop over the rows in the data table
    for row in dataTable.GetRows(rowsToInclude, columnCursor):
        rowIndex = row.Index
        ## use the cursor to compare the column row value to our drop down value
        if int(columnCursor.CurrentValue) > int(dropDownValue):
                ## if it passes the condition add it to our new indexset
                rowsToMark.AddIndex(rowIndex)
else:
    ## if the user has selected None as the option for the drop down, clear the indexset
    rowsToMark.Clear()
## Now finally mark the rows in the data table
Document.ActiveMarkingSelectionReference.SetSelection(RowSelection(rowsToMark), dataTable)

Refresh data without using automation services

This script starts or stop a timer that triggers a Spotfire Action Control.  In this case, the spotfire control refreshes an underlying data table.



html

<style>
.light{
  position:absolute;
  top:50px;
  width: 7px;
  height: 7px;
  border-radius: 50%;
  background-color: lightgray;
  cursor:pointer;
}

.on{
  background-color: #ABFF00;
  box-shadow: 
    rgba(0, 0, 0, 0.2) 0 -1px 7px 1px, 
    inset #304701 0 -1px 9px,
    #89FF00 0 2px 14px;
}
</style>

<span id=debug xxxstyle='display:none'>--</span>
<span class="light" id='lightButton'></span>
<span id='reloadButton' style='display:none'>
   <SpotfireControl id="36d875690f344e45b9ef764058a3ed32" />
</span>

js
var pid
var speed = 300//click the spotfire button every 5 mins 

start = function(){
 pid = setInterval(function(){
    $("#reloadButton input").click()

$('#lightButton').hasClass("on")?
$('#lightButton').removeClass("on"):
$('#lightButton').addClass("on")

 },speed*1000)
 $('#lightButton').addClass("on")
 $("#reloadButton input").click()
 $("#lightButton").attr('onclick',"stop()")
 $('#debug').text("process "+started+" started ")
}

stop = function(){
 clearInterval(pid)
 $('#debug').text("process "+started+" stopped ")
 $('#lightButton').removeClass("on")
 $("#lightButton").attr('onclick',"start()")
}

$("#lightButton").attr('onclick',"start()")



script
#dt is a data table script parameter
#refreshes the data
dt.Refresh()






Position Spotfire Controls freely




html
<style>
#aCalcVal{position:absolute;top:30px; left:122px;}
#aBulletGraph{position:absolute;top:608px; left:204.6875px;}
#aDropDown{position:absolute;top:141px; left:91.6875px;}
#anInput{position:absolute;top:563px; left:222.6875px;}
</style>

<IMG src="5487af1ed6ec4b8c90da59da34200d0f.png" border=0>

<span  id='aCalcVal'>
<SpotfireControl id="0ec42a6609d541308d42c406369df8d5" />
</span>

<span id='aBulletGraph'>
<SpotfireControl id="e57d92f8099c438b9a2a003b413fef37" />
</span>

<span id='anInput'>
<SpotfireControl id="0b36814498214275a80596860f6abcd7" />
</span>

<span id='aDropDown'>
<SpotfireControl id="9c8c9f42216a4efc8bb63ce81113d9d4" />
</span>


Optionally add this javascript tool to stop guessing top and left positions.

js
$(".drag").draggable({
 cursor:"move",
 drag:function(e){
p =  $(this).position() 
id = $(this).attr('id')
css = "#"+id+"{position:absolute;top:"+p.top+"px; left:"+p.left+"px;}"
$("#pos").text(css)  
 }
 ,refreshPositions :true,

}).css({border:"1px dashed blue",padding:"5px"})

This tool requires to add class='drag' to all elements you wish to move, for example:
<span class='drag' id='aDropDown'>
<SpotfireControl id="9c8c9f42216a4efc8bb63ce81113d9d4" />
</span>

It also requires a way to display the position:
<div id="pos">--</div>