I don't believe that's possible with one parameter - it used to be that you could do this in some sources because (I think) Tableau was not encapsulating the parameter properly.
Now a parameter set to 'site 1','site 2', 'site 3' becomes
Where [Customer Name] in ('''site 1'',''site 2'', ''site 3''')
because the parameter plus any extra ' are encapsulated. However, multiple parameters works:
Where [Customer Name] in (<Parameters.Parameter 1>, <Parameters.Parameter 1 (copy 1)>)
Where is the csv list coming from ?
In a related question, why custom SQL instead of filtering?
Thanks for the response, we definitely noticed the encapsulation of the values and thought maybe Tableau had a concept of an escape character that we could leverage.
The comma separated values come from a web service call we are making that allows us to implement row level security. First, the PHP wrapper authenticates the user, and in doing so receives an array of products that they are allowed to view.
When they hit a dashboard we would like to pass that list of values into the "IN" portion of the where clause of the data source query so that the result is a filtered list of products based off of the array that we are passing in the embed script.
I know there are many different ways to implement product security but due to limitations of tableau blending, we feel this would be our most optimal approach.
Sent from my iPhone
To understand why we're not filtering let me describe a general use case. (This is slightly different than the question at hand but pretty much describes why we aren't "filtering")
Data source: 20 mil records of transactional sales data from Superstore.
Product Security Requirements: there are 300 users at superstore, all of which have a different set of products they are allowed to see data from. Each users averages around 300 different products that they have access to.
This information is stored in a table like below:
Username | product
User1 | A1
User1 | A5
User1 | B6
User2 | A1
User2 | F5
To get this product data into the same DB as our transactional data requires an ETL process that we would like to eliminate. But until then.......
To apply user filtering all we would have to do is modify the query to:
select * from transactions a join product_security b on a.product = b.product
We then can create a calculation in Tableau: [Username] = username()
throw that on the filters shelf, set to true and be done.
The problem is the 20 mil records part. A live connection is not feasible at that number of records so an extract is necessary.
An extract with the query above creates a cartesian product, essentially blowing the data up to 20 mil X 300.(Not a usable solution)
Blending should solve this problem but it doesn't.
data source 1: transactional data (Primary)
data source2: product_security table (Secondary)
has data source filter of [Username] = username() set to true
Join key is product.
add a secondary data source filter of product set to exclude NULLs. (Filters out products that don't have a match in the secondary source i.e. product security portion)
This works perfectly until you want the end user to be able to filter products with a quick filter.
If you drag the primary [product] dimension on to filters shelf the user has the entire list of products to select from irrespective of their product security. Show fewer values does not take into account the secondary filter. (This is not usable)
If you drag a copy of the secondary data source dimension of [product] you see all of the products in the secondary data source (All 300 available to that specific user irrespective of which products are in the data, also unusable)
The way I am currently getting around this is by creating a sheet with the "exclude nulls" filter after blend to get the subset of products that exist in both tables.
I then use this sheet with a click action on its list of products to filter the view with the transactional data so that only clicked products get passed and when no value is selected, no transactional data shows.
Very inconvenient interaction for the end user and just doesn't look clean (no native dropdown feature etc....)
I've managed to do the SQL injection trick you're looking for with an IN() list for a numeric column. I've not tried with quoted strings, but I suspect that Alex is right that those will get escaped too much.
It's pretty convoluted, with a RAWSQL boolean expression and some trickery with deliberately unmatched quotes which get matched up with the quotes Tableau puts in.
Assuming you can use a numeric factoryId, this is what your ideal SQL would look like:
select * from table
where factoryId in (1, 2, 3)
What you can achieve will look like this:
select * from table
where '' = '' and factoryId in (1, 2, 3) and '' = ''
The '' = '' expressions are needed to match up the quotes and obviously both resolve to true.
You would have a string parameter like this:
[parameters].[factoryList] = "1, 2, 3"
and a string calculated field like this:
[subqueryExpression] = " = ' and factoryId in (" + [parameters].[factoryList] + ") and ' = "
Just to be explicit, that is double quotes containing the fragments of that expression and then a single single-quote after the first equals and another single single-quote before the last equals. All will become clear (hopefully).
Then you need a calculated filed with a RAWSQL expression like this:
[filterCondition] = RAWSQL_BOOL('''%1''', [subqueryExpression])
That's 3 single-quotes before and 3 after the %1.
The first single-quote starts a string, the next two are treated as an escaped single quote inside the string, so generate one single-quote in the result. Tableau then wraps the %1 in single-quotes, which means your resulting expression starts with '' (two single-quotes). Then the single single-quote after the equals sign is escaped by Tableau (because it's embedded in a quoted string), so that becomes another pair of single-quotes. Same thing in reverse at the other end.
Took a bit of trial and error, but trust me, it works.
It may be time to look at those different methods, or passing multiple parameters (though I still don't really see how that works), because a single string won't work.
Slight correction - I posted that from memory and got it slightly wrong. That was one of the wrong ones out of the "trial and error" bit!
I tried it like that initially, so that the parameter just contained the list, but I didn't get that going - so I ended up having to put the whole SQL expression in the parameter. So instead of the parameter and the [subqueryExpression] calculated field I show above, just have the parameter like this:
[parameters].[subquery] = " = ' and factoryId in (1, 2, 3) and ' = "
and substitute that straight into RAWSQL like this:
[filterCondition] = RAWSQL_BOOL('''%1''', [parameters.][subquery])
Having the whole SQL expression in the parameter sounds a bit ugly, but if you are constructing it all from the API it isn't user visible.
I just dug out the example where I used it and this version definitely works.
The other thing I forgot to mention is that you get an error thrown when you first put that on the filter shelf. You have to ignore it and go ahead and add a value of true (which you have to enter as "1" in the filter dialog).
But with enough perseverance it does work.
Thanks Richards, very creative approach. I'll mark as correct, unfortunately though I must pass strings because maintaining a reference table of integers will be too cumbersome. I appreciate the help from all and hope soon that Tableau will account for this scenario in the custom SQL logic as I feel this could be a somewhat common scenario.
I am glad I found this discussion topic as I believe my issue could be somewhat similar to this.
Instead of passing multiple string values on my parameter, I am actually trying to pass an entire sql query as a string. I have a field on the table called query, and it stores full queries as string in it.
I then would like to use the parameter by itself as the custom query for the connection. However, it seems that tableau wraps the statement with single quotes ' and i am not able to execute it. Do you guys have any suggestions on working around this?
[Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AnalysisException: Syntax error in line 3:
'select * from `schema`.`table`'
Encountered: STRING LITERAL
Expected: SELECT, VALUES, WITH
CAUSED BY: Exception: Syntax error
Sorry to say I very much doubt that you are going to be able to achieve this using the technique I described here. I spent a long time trying to come up with a way of passing strings in to raw SQL or Custom SQL without success. My method above only worked with integers.
The only other option I can think of which might be possible with some databases (I've never worked with Cloudera) is to have a stored procedure which executes dynamic SQL and execute the stored procedure from Tableau. I have no idea if that even has a chance of working - it's just the only other straw I can think of to grasp at.
Thanks for your feedback. Hopefully Aaron might have had better luck on his issue and will share his thoughts some time...
So have you ever been able to create a dynamic custom query in tableau? Not just the where clause using parameters, but the entire query itself...
I have tried your solution but I am not able to get it to work.
I have the parameter string set to:
"=' and PlanID in (2000000, 2000001, 2000002) and '="
and calculated field as:
I receive the following error
[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '='' and PlanID in (2000000, 2000001, 2000002) and ''='.
[Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
Any idea why this isn't working?
Well I just tried it again myself against SQL Server and it worked for me, so my guess is that you haven't got the quotes exactly as they need to be. I remember that it was really hard to express that clearly when I wrote it and I just had to experiment again myself to work out what I meant.
My guess is that you may have included the double quotes surrounding the parameter value in the actual parameter. What I was trying to say was that the parameter needs to contain the string inside those quotes, but not the quotes themselves. So your parameter should have everything on the next line and no more:
=' and PlanID in (2000000, 2000001, 2000002) and '=
The RAWSQL expression looks exactly right.
I just tried putting the extra double-quotes into the parameter and got the same error as you did, so I think that must be what the problem is.
As I mentioned, you will get an error when you first put the calculated field on the filter shelf - but if you just keep going it should work.
This really is an ugly hack, isn't it?
I had to face a similar issue and after struggling a bit, this is what I came up with:
CASE WHEN (<Parameters.SEARCH PLAN#>) = '' THEN 1=2 ELSE ( concat( '(', regexp_replace(<Parameters.SEARCH PLAN#>,',','\',\''), ')') RLIKE (plan_n) ) END)