Intercept click on KPIs to mesure frequency of utilisation by users

[Manuel Novo Lopes]

Hi everyone,

I would like to create a script that every time a user click on a KPI, data is send to a excel or a database.

The information I need is just the time ofthe click on the KPI by the user (and if possible the IDof the user cliking).

Under "Perfom action on click", I saw that I can implement a scrip with IronPython.

I would like to find an exemple of a script that solve my problem.

I am a novice at Spotfire. :)

Thanks,

Manuel

[Tyger Guzman 2]

You can use a python script to write back to a database :

In the example I'm using the windows authentiction 'Trusted_Connection=yes' you can also change this out with

dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient","Server=localhost;Database=myDB;UID=myuser;PWD=mypass",sqlCommand)to provide shared credentials instaed of Windows authentication.

 

In the dialog box uncheck Execute In Transaction. This script will show a progess bar each time it runs.

#Imports for Progress Bar

from Spotfire.Dxp.Framework.ApplicationModel import *

import time

ps = Application.GetService[ProgressService]()

#Update DB Imports

from Spotfire.Dxp.Data.Import import DatabaseDataSource

from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings

#Date Time

import datetime

#Current User

from System import Environment

#Capture Current Date

today=datetime.datetime.today().strftime("%m/%d/%Y %H:%M:%S")

today = str(today)

 

def execute():

try:

# simulates an indeterminate step

ps.CurrentProgress.ExecuteSubtask("Updating DB");

RunUpdate() # do some work

time.sleep(2) # do some work

ps.CurrentProgress.CheckCancel()

except: # user canceled

pass

 

def RunUpdate():

sqlCommand = 'Insert Into [Test_Write_Back] ([userName],[TimeClicked])'

sqlCommand += 'Values('+Environment.UserName+ ',' + "convert(datetime,'" + str(today) + "',0)" +')'

print(sqlCommand)

dbsettings = DatabaseDataSourceSettings("System.Data.SqlClient","Server=ServerName;Database=DataBaseName;Trusted_Connection=yes;",sqlCommand)

ds = DatabaseDataSource(dbsettings)

newDataTable = Document.Data.Tables.Add("temp",ds)

Document.Data.Tables.Remove(newDataTable)

 

ps.ExecuteWithProgress("Update DB", "SQL Commands Being Sent", execute)To not have progress show leave the check box in Execute in Transaction and use :

#Imports for Progress Bar

from Spotfire.Dxp.Framework.ApplicationModel import *

import time

ps = Application.GetService[ProgressService]()

#Update DB Imports

from Spotfire.Dxp.Data.Import import DatabaseDataSource

from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings

#Date Time

import datetime

#Current User

from System import Environment

#Capture Current Date

today=datetime.datetime.today().strftime("%m/%d/%Y %H:%M:%S")

today = str(today)

 

def execute():

try:

# simulates an indeterminate step

#ps.CurrentProgress.ExecuteSubtask("Updating DB");

RunUpdate() # do some work

#time.sleep(2) # do some work

#ps.CurrentProgress.CheckCancel()

except: # user canceled

pass

 

def RunUpdate():

sqlCommand = 'Insert Into [Test_Write_Back] ([userName],[TimeClicked])'

sqlCommand += 'Values('+Environment.UserName+ ',' + "convert(datetime,'" + str(today) + "',0)" +')'

print(sqlCommand)

dbsettings = DatabaseDataSourceSettings("System.Data.SqlClient","Server=ServerName;Database=DataBaseName;Trusted_Connection=yes;",sqlCommand)

ds = DatabaseDataSource(dbsettings)

newDataTable = Document.Data.Tables.Add("temp",ds)

Document.Data.Tables.Remove(newDataTable)

 

#ps.ExecuteWithProgress("Update DB", "SQL Commands Being Sent", execute)

execute()

[Manuel Novo Lopes]

Thank you so much for such an elaborate anwser !

 

I am having a hard time understanding everything kwowing that I barely know Spotfire or IronPython.

 

I am currently working on that :) 

 

Could you, if it's not to much to ask, provide a little more information about what's going on in your script

 

Thanks, 

 

Manuel 

[Tyger Guzman 2]

The most important part of the script is : 

 

def RunUpdate():
sqlCommand = 'Insert Into [Test_Write_Back] ([userName],[TimeClicked])' 
sqlCommand += 'Values('+Environment.UserName+ ',' +  "convert(datetime,'" + str(today) + "',0)" +')'	 
print(sqlCommand)	
dbsettings = DatabaseDataSourceSettings("System.Data.SqlClient","Server=ServerName;Database=DataBaseName;Trusted_Connection=yes;",sqlCommand)
ds = DatabaseDataSource(dbsettings)
newDataTable = Document.Data.Tables.Add("temp",ds)
Document.Data.Tables.Remove(newDataTable)

 

Which is creating a SQL statement to send to a SQL server.

 

 

 

the pevious steps in the code are capturing the username and a time stamp 

 

#Current User
from System import Environment
username = Environment.UserName 
#Capture Current Date
today=datetime.datetime.today().strftime("%m/%d/%Y %H:%M:%S")	
today = str(today)

 

 

 

by attaching this script to the Action when a KPI is clicked - each time the click happens the current user and datetimestamp is uploaded to a table I named [test_write_back]

[Manuel Novo Lopes]

Thanks you for you help !

 

Could I add the name of the current KPI next to the user name and date

 

Thanks !

[Tyger Guzman 2]

To add the KPI that is being selected you'd have to capture the MARKING : 

 

 

 

you can add this snippet to your code to also find the values that are marked in the visual: 

 

from System.Collections.Generic import List
from Spotfire.Dxp.Data import *

# Create a cursor for the table column to get the values from.
# Add a reference to the data table in the script.
dataTable = Document.Data.Tables["Table"]
cursor = DataValueCursor.CreateFormatted(dataTable.Columns["ColumnName"])

# Retrieve the marking selection
markings = Document.ActiveMarkingSelectionReference.GetSelection(dataTable)

# Create a List object to store the retrieved data marking selection
markedata = List [str]();

# Iterate through the data table rows to retrieve the marked rows
for row in dataTable.GetRows(markings.AsIndexSet(),cursor):
#rowIndex = row.Index ##un-comment if you want to fetch the row index into some defined condition
value = cursor.CurrentValue
if value <> str.Empty:
	markedata.Add(value)

# Get only unique values
valData = List [str](set(markedata))

# Store in a document property
yourVariableName = ', '.join(valData)

print(yourVariableName)