Cross Table Data to 2D Array Python - Can be used for Loop Through

In Spotfire,we have direct option to loop thru Data Table but not for Cross Table.

Below is the python script to capture the Cross Table Data into 2D Array and then it can be used for Loop Thru as per your requirement.

import System

from System.IO import *

from Spotfire.Dxp.Data import *

from Spotfire.Dxp.Application.Visuals import *

from Spotfire.Dxp.Data.Import import *

from System import Byte

memStream = MemoryStream();

sWriter = StreamWriter(memStream);

#Exporting the data to Memory Stream

crossTable.As[CrossTablePlot]().ExportText(sWriter); 

memStream.Seek(0, SeekOrigin.Begin);

sReader = StreamReader(memStream);

str="";

# 2D Array

lst=[[]]; 

i=0;

#Reading Memory Stream and then saving it to 2D Array

while(sReader.Peek() >=0):

 a=sReader.ReadLine();

 a=a.replace("\t", "!");

 temparray=a.split("!");

 j=0;

 count=len(temparray);

 #print count

 while j < count:

lst[i].append(temparray[j]);

j=j+1;

 i=i+1;

 lst.append([])

del lst[len(lst)-1]

 #Final Arrary

print lst 

Triggering Python Script on Dashboard Open ( Example: Tab Level Security)

 
Prerequisites:-
 - Property Controls/Document Property
 - Python Script
 - Statistical Server in your project (TERR)
 - Personalized Information link

In Spotfire, we can apply Data level security using Personalized information links i.e., you can set up a data source to return only applicable information for a certain user or group. 
Example:- To return Asia Region sales data for Asia User group.

So restricting the data is possible to do but how about Tab Level Security at Dashboard?

Yes, its possible to implement Tab Level security using Python script. Build a Python script which should read the Current User/Current group based upon your requirement and implement the logic's accordingly. Lets take a simple example that the Dashboard has 2 tabs; Summary Tab & Details Tab and would like to restrict the Details Tab for a specific User group "Restricted" (Spotfire Group).

Assuming we have created the Python script and now the next step here would be how to trigger the Python Script on Opening of Dashboard? 

Below are the Detail steps to achieve our goal:

Step 1: Create a Data Table based on personalized Information link which should fetch the data whether the Current User belongs to Restricted group or not. The Query should like below:-

SELECT
   U1."USER_NAME" AS "USERNAME",
   G2."GROUP_NAME" AS "GROUPNAME"
FROM
   "SPOT_USR"."USERS" U1,
   "SPOT_USR"."GROUPS" G2,
   "SPOT_USR"."GROUP_MEMBERS" G3
WHERE
   (G3."MEMBER_USER_ID" = U1."USER_ID")
   AND (G2."GROUP_ID" = G3."GROUP_ID")
   AND (U1."USER_NAME" in (%CURRENT_USER%))
   AND (G2."GROUP_NAME" = 'Restricted')
   AND <conditions>

Step 2:  Python Script for deleting the page "Detail" is as below

for Page in Document.Pages:
    if Page.Title == "Detail":
        Document.Pages.Remove(Page) 

 Step 3: Now either update the python script to read Usergroup from DataTable which is based on Personalized Information or Assign the Usergroup value to a Document Property thru R-script

 a) Python Script to Read User Group from Data Table.

table=Document.Data.Tables["PersonalizedInformationLinkTable"]
minCol=table.Columns['GroupName']
minCursor=DataValueCursor.Create(minCol)

for row in table.GetRows(minCursor):
  Document.Properties["UserGroup"]= minCursor.CurrentValue;


b) Assign the Usergroup value to a Document Property thru R-script.

Register a new Data Function(TERR) with the script as below and define Input and Output Parameters  as Value

    DocProperty <- Input


 







Click on Run button and choose the Input as expression with definition as 
Max([PersonalizedInformationLinkTable].[GROUP_NAME])


 
Similarly, choose Output as Document Property and choose the property accordingly.





Step 3: Final Python script would look like

import Spotfire.Dxp
from Spotfire.Dxp.Data import *

#-------------Below lines if we fetch usergroup thru Python -----------------


table=Document.Data.Tables["PersonalizedInformationLinkTable"]
minCol=table.Columns['GroupName']
minCursor=DataValueCursor.Create(minCol)
for row in table.GetRows(minCursor):
    Document.Properties["UserGroup"]= minCursor.CurrentValue; 

#--------------------------------------------------------------------------

 Document.Pages:
    if Document.Properties["UserGroup"]=="Restricted":
        if Page.Title == "Detail":       
        Document.Pages.Remove(Page)


Step 4: Create a Document Property called "DateTimeStamp" and assign the above the script which we have created.

Step 5: Create a simple Information link with one element that should fetch current DateTimeStamp

SELECT
   SYSTIMESTAMP AS "SYSTEMTIME"
FROM
   "SYS"."DUAL" D1
WHERE
   <conditions>

Step 6: Import this table to your Dashboard and assign the  SystemTime Element to the Document Property "DateTimeStamp" using R script (similar to steps 3b)

Step 7: Save the Dashboard

Now, as you Open or Refresh the Dashboard each time, the Time Stamp will be different & the document property will get updated and hence the Python script will be triggered.


PS: We can Trigger Python script on Opening of Dashboard using JScript too.