How to pass multiple values to one stored procedure parameter in Soptfire

[Sanandh C k]

Hi Team,

I have created one dashboard by using stored procedure . I have one String type parameter in that procedure. But I am unable to pass multiple selected value from list box property to stored procedure . Please suggest a solution for this. The storedprocedure is taking only one value at time .

[Shandilya Peddi]

Hello Sanandh,

The workaround is to save the multiple values to another document property as a comma separated values and then pass this document property values to your stored procedure. In you stored procedure you will have to handle this comma separated values

[Sanandh C k]

Hi Shandilya ,

 

Thanks for your solution. I had tried with this solution in my dashboard and it works well.

[Sanandh C k]

Here is the answer.

Steps for implementing the solution.

1. Add list box filter to the text area

2. Create a document property for storing the values

3. Write Iron python script for updating selected values to document property and refresh the data table

4. Add a button to executing the script

from Spotfire.Dxp.Application.Filters import *

from Spotfire.Dxp.Data import *

from Spotfire.Dxp.Application import Filters as filters

from Spotfire.Dxp.Application.Filters import ListBoxFilter

from Spotfire.Dxp.Data import IndexSet

from Spotfire.Dxp.Data import DataValueCursor

import Spotfire.Dxp.Data.DataTable

from Spotfire.Dxp.Data import *

from Spotfire.Dxp.Data import DataValueCursor

 

###Created By Sanandh @17 Jul 2019

 

######For Take getting parameter ######

 

tableName = 'MyTable' ###### Data table name

myTable = Document.Data.Tables[tableName]

 

filt=Document.FilteringSchemes[0][myTable][myTable.Columns["MyColumn"]].As[ListBoxFilter]()

###### Assign the corresponding filter schema name

 

rowCount = myTable.RowCount ###### Checking row count

 

#print rowCount

#print "++++++++++++ ++++++++++++++++++++++ ++++++++++++"

Param = ''

if myTable.RowCount == filt.FilteredRows.Count:

param = ''

else :

 

rows = Document.ActiveFilteringSelectionReference.GetSelection(Speciality).AsIndexSet()

 

for row in rows:

for column in Speciality.Columns:

if (column.Name == "MyColumn"): ## Column Name

Values= (column.RowValues.GetFormattedValue(row))

#print Values

Param = Param + Values + "||"

 

Document.Properties['MyDoc']=Param /// Assign value to doc proprty

print Document.Properties["MyDoc"]

 

if table.IsRefreshable and table.NeedsRefresh: /// Refresh Table

table.Refresh()

[Sanandh C k]

Function for spli stringsSET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GOALTER FUNCTION [datalayer].[Fn_Split] (  

      @InputString                  VARCHAR(8000),  

      @Delimiter                    VARCHAR(50)  

)  

  

RETURNS @Items TABLE (  

      Item                          VARCHAR(8000)  

)  

  

AS  

BEGIN  

      IF @Delimiter = ' '  

      BEGIN  

            SET @Delimiter = '||'  

            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)  

      END  

  

      IF (@Delimiter IS NULL OR @Delimiter = '')  

            SET @Delimiter = '||'  

  

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic  

--INSERT INTO @Items VALUES (@InputString) -- Diagnostic  

  

      DECLARE @Item                 VARCHAR(8000)  

      DECLARE @ItemList       VARCHAR(8000)  

      DECLARE @DelimIndex     INT  

  

      SET @ItemList = @InputString  

      

      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)  

      WHILE (@DelimIndex != 0)  

      BEGIN  

            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)  

            INSERT INTO @Items VALUES (@Item)  

  

            -- Set @ItemList = @ItemList minus one less item  

            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+2, LEN(@ItemList)-@DelimIndex)   -- changed to 2 beacuse deliminator is 2 sanandh  

            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)  

      END -- End WHILE  

  

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString  

      BEGIN  

            SET @Item = @ItemList  

            INSERT INTO @Items VALUES (@Item)  

      END  

  

      -- No delimiters were encountered in @InputString, so just return @InputString  

      ELSE INSERT INTO @Items VALUES (@InputString)  

  

      RETURN  

  

END -- End Function  

[Sanandh C k]

This is the latest code

 

from Spotfire.Dxp.Application.Filters import ListBoxFilter

#get a reference to a listbox 
filt=Document.FilteringSchemes[0][myDataTable][myDataTable.Columns["Province"]].As[ListBoxFilter]()

#to get the active filtering reference:
#filt = Document.FilteringSchemes[Document.ActiveFilteringSelectionReference][myDataTable][MyDataTable.Columns["symbol"]].As[ListBoxFilter]()

#loop selected values
SelectedValues=''

for value in filt.SelectedValues:
print value
SelectedValues= SelectedValues+value+','
SelectedValues=SelectedValues[:-1]
if filt.IncludeEmpty==True:
SelectedValues=SelectedValues+',(Empty)'

if filt.IncludeAllValues == True:
SelectedValues='All'
if filt.IncludeAllValues == False and len(SelectedValues)==0:
SelectedValues="No Values selected"

Document.Properties["SelectedValues"]=SelectedValues

 

 

[Sanandh C k]

dkjfhdsfjh