1 2 Previous Next 15 Replies Latest reply on Aug 17, 2018 4:09 PM by VBUG

    Comma separated values in Custom SQL Parameters

    Aaron Clancy

      Im sure this has been asked before but:

      Is it possible to pass comma separated values into a custom SQL parameter.

      ie. 

      select * from table where factory in (<parameter.factoryList>)

       

      Parameter    factoryList =  site1,site3,site8

       

      we're willing to add any sort of extra characters or escape characters if need be.

        • 1. Re: Comma separated values in Custom SQL Parameters
          Alex Kerin

          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)>)

          • 2. Re: Comma separated values in Custom SQL Parameters
            Alex Kerin

            Where is the csv list coming from ?

            In a related question, why custom SQL instead of filtering?

            • 3. Re: Comma separated values in Custom SQL Parameters
              Aaron Clancy

              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

              • 4. Re: Comma separated values in Custom SQL Parameters
                Aaron Clancy

                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")

                 

                Hypothetical

                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:

                 

                [product_security]

                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.

                Blending approach.

                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....)

                • 5. Re: Comma separated values in Custom SQL Parameters
                  Richard Leeke

                  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.


                  • 6. Re: Comma separated values in Custom SQL Parameters
                    Alex Kerin

                    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.

                    • 7. Re: Comma separated values in Custom SQL Parameters
                      Richard Leeke

                      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.

                      • 8. Re: Comma separated values in Custom SQL Parameters
                        Aaron Clancy

                        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.

                         

                        Thanks!

                        • 9. Re: Comma separated values in Custom SQL Parameters
                          Lucas Brito

                          Hi Richard Leeke and Aaron Clancy,

                           

                          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

                          • 10. Re: Comma separated values in Custom SQL Parameters
                            Richard Leeke

                            Hi Lucas

                             

                            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.

                             

                            Richard

                            • 11. Re: Comma separated values in Custom SQL Parameters
                              Lucas Brito

                              Hi Richard,

                               

                              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...

                              • 12. Re: Comma separated values in Custom SQL Parameters
                                kj.jeyanthan

                                Hi Richard,

                                 

                                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:

                                RAWSQL_BOOL('''%1''', [Parameters].[PlanID])

                                 

                                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?

                                 

                                 

                                Thanks,

                                KJ

                                • 13. Re: Comma separated values in Custom SQL Parameters
                                  Richard Leeke

                                  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?


                                  • 14. Re: Comma separated values in Custom SQL Parameters
                                    sree Tatineni

                                    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)

                                    1 2 Previous Next