Adding parameters to custom sql query using 'in' clause in Tableau

0
votes

Source Database - Teradata
BI Tool - Tableau

I have created a report in Tableau where in am fetching some data using custom sql query, the query is accessing data from Teradata database. Consider the below sql query as an example:

    Select Tab1.Col1, Tab1.Col2, Tab2.Col3 from Tab1 
    inner join Tab2 on Tab1.col1 = Tab2.col1 
    where Tab1.Col1 in ('Alpha','Beta','Gamma')

Tableau reports works fine and data is retrieved.

Created a string parameter in Tableau with name P_RAYS. And passed this parameter to the custom sql query as shown below:

    Select Tab1.Col1, Tab1.Col2, Tab2.Col3 from Tab1 
    inner join Tab2 on Tab1.col1 = Tab2.col1 
    where Tab1.Col1 in (<Parameters.P_RAYS>)

When only Alpha is passed to the parameter, tableau retrieves the data and report is generated accurately but when Alpha, Beta and Gamma is passed to the parameter, no data is returned in tableau.

It looks like inside custom sql query, the parameters are being processed incorrectly as 'Alpha,Beta,Gamma' and not as 'Alpha','Beta','Gamma'

2
sqltableau-apiteradatareportingbusiness-intelligence
See stackoverflow.com/a/33522563/2527905 – dnoeth

2 Answers

0
votes

The easiest way to solution this would be to create multiple parameters, one for each value, then pass each parameter in your SQL In statement. This will work if you always have a small number of parameters to pass (<=10) and pass the same number of parameters each time. For example Parameter 1 = string value'Alpha', parameter 2 = string value 'Beta' and parameter 3 = string value 'gamma'. Then SQL is Tab1.Col1 in (<Parameter 1>,<Parameter 2>, <Parameter 3> ). We typically create a control page in the workbook to explain and set these parameters and filters for the end users. If the number of parameters sometimes changes, you could either set the unused ones to the same values as the used ones, or NULL, if NULL value is not something found in your data set. If you need to have >10 list items, then another approach like using sets or filter using a different field value and filtering this field in the workbook would be a better option.

0
votes

This is how I did it for parameters with a '|' separator:

SELECT [COL1], [COL2], [COL3], [COL4] 

FROM [TABLE1]

WHERE 

COL1 IN(SELECT * FROM string_split(<Parameters.MultiValueFilterParam1>,'|')) AND
COL2 IN(SELECT * FROM string_split(<Parameters.MultiValueFilterParam2>,'|')) AND
COL3 = <Parameters.SingleValueFilterParam1> AND
COL4 BETWEEN <Parameters.RangeValueFilterParamMIN> AND <RangeValueFilterParamMIN>