Can the Custom SQL Query in a Tableau Dashboard accept a list of values in a Parameter?

Question

I have a Tableau dashboard drawing data from a Vertica Database via a Custom SQL Query.

The database table contains more than 100 million rows, with a column COL1 indicated as primary key. Each COL1 value corresponds to exactly one row of data. Therefore COL1 is unique for all rows.

The Custom SQL Query below refreshes the dashboard whenever the parameter is updated.

SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE WHERE COL1=<Parameters.Col1Param>

Can the dashboard users input more than one value to get more than 1 row of data?

I have tried using the IN condition as below:

SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE WHERE COL1 IN (<Parameters.Col1Param>)

However, I can't seem to be able to make this work with Parameter values Param1;Param2;Param3 or Param1,Param2,Param3.

I also tried including all values of COL1 and allowing the user to filter on-the-fly, but the database table is too large (over 100M of rows) for the dashboard to load the data into memory.

I’m not certain, but I think this can work with a parameter that gets set to a commas separated list of quoted strings — i.e. the parameter has the value “V1”, “V2”, “V3”. That’s why parameter actions in Tableau have the option to create comma separated lists, the trick is to use a calculated field to include the quotes. — Alex Blakemore

paperclip paperclip · Accepted Answer · 2021-03-10T09:54:44

As always, minutes after posting a question on StackOverflow, I find a reasonable answer to my question.

The answer to this can be found here: Convert comma separated string to a list

SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE WHERE COL1 IN (
    SELECT SPLIT_PART(<Parameters.Col1Param>, ';', row_num) params
    FROM (SELECT ROW_NUMBER() OVER () AS row_num FROM tables) row_nums
        WHERE SPLIT_PART(<Parameters.Col1Param>, ';', row_num) <> ''
)

Can the Custom SQL Query in a Tableau Dashboard accept a list of values in a Parameter?

1 Answers