12 Replies Latest reply on Aug 24, 2016 10:26 AM by Gagan Thapar

    Can I build dynamic custom sql query based on user input?

    Gagan Thapar

      Can I achieve the following in tableau through some script?

       

      I want to customize the query based on the user input. This is my base query

       

      select * from TA, TB

      where TA.a = TB.b.

       

      if the user input is <PARAMETER 1>, then I would build the following query:

       

      select * from TA, TB

      where TA.a = TB.b.

      and TA.a1. = <PARAMETER 1>.

       

      If the user input is <PARAMETER 2>, then my query would be:

       

      select * from TA, TB

      where TA.a = TB.b.

      and TB.b1. = <PARAMETER 1>.

       

      Basically I want to put conditions on different columns based on the input. I know some reporting tools can do that by script.

        • 1. Re: Can I build dynamic custom sql query based on user input?
          kettan

          You could do this with a CASE condition = CASE condition.

           

          The reason the SQL syntax below looks strange is because it is SQL Jet with Excel Legacy Connector:

           

          select *

          from [x$] x, [y$] y

          where x.a = y.a and

          IIF( LEN(<Parameters.Parameter 1>) = 0 AND LEN(<Parameters.Parameter 2>) = 0, '',

          IIF( LEN(<Parameters.Parameter 1>) > 0, x.b, y.c ))

          =

          IIF( LEN(<Parameters.Parameter 1>) = 0 AND LEN(<Parameters.Parameter 2>) = 0, '',

          IIF( LEN(<Parameters.Parameter 1>) > 0, <Parameters.Parameter 1>, <Parameters.Parameter 2> ))

           

          I see that you asked the same question two months ago. If my answer is a correct answer, it would be helpful if you answered your own question in  How to change the query based on input parameters?  and marked it correct. You could reference this thread there.

           

          Attached Workbook Version:  9.0

          .

          1 of 1 people found this helpful
          • 2. Re: Can I build dynamic custom sql query based on user input?
            Gagan Thapar

            Can you please check if I understood it correctly?

             

            select *

            from [x$] x, [y$] y

            where x.a = y.a and

            IIF( LEN(<Parameters.Parameter 1>) = 0 AND LEN(<Parameters.Parameter 2>) = 0, '',

            IIF( LEN(<Parameters.Parameter 1>) > 0, x.b, y.c ))

            =

            IIF( LEN(<Parameters.Parameter 1>) = 0 AND LEN(<Parameters.Parameter 2>) = 0, '',

            IIF( LEN(<Parameters.Parameter 1>) > 0, <Parameters.Parameter 1>, <Parameters.Parameter 2> ));

             

             

            Base query is:

             

            select *

            from [x$] x, [y$] y

            where x.a = y.a

             

            if the <Parameter 1> is not empty, then the query is

             

            select *

            from [x$] x, [y$] y

            where x.a = y.a and

            x.b = <Parameter 1>

             

            If the <Parameter 2> is not empty, then the query is:

             

            select *

            from [x$] x, [y$] y

            where x.a = y.a and

            y.c = <Parameter 2>

             

            I am using the oracle database, it doesn't provide IIF function, but I can use decode for that.

             

            Thanks a lot for your help.

            • 3. Re: Can I build dynamic custom sql query based on user input?
              kettan

              Here is the Oracle syntax:

               

              CASE

              WHEN LENGTH(<Parameters.Parameter 1>) = 0 AND LENGTH(<Parameters.Parameter 2>) = 0 THEN ''

              WHEN LENGTH(<Parameters.Parameter 1>) > 0 THEN x.b

              ELSE y.c

              END

              =

              CASE

              WHEN LENGTH(<Parameters.Parameter 1>) = 0 AND LENGTH(<Parameters.Parameter 2>) = 0 THEN ''

              WHEN LENGTH(<Parameters.Parameter 1>) > 0 THEN <Parameters.Parameter 1>

              ELSE <Parameters.Parameter 2>

              END

               

              In case it doesn't work, it is probably caused by some minor typing errors.

              Please let me know if this works and if not what the error message is.

               

              And one more thing. I am not sure if you want the query to return everything when both parameters are empty. If not, just remove the first WHEN condition in both CASEs.

              • 4. Re: Can I build dynamic custom sql query based on user input?
                Gagan Thapar

                This will work, that's what I was thinking of. I was wondering what can I do if I have 3 parameters? Is it advisable to have different data sources for these kind of reports?

                • 5. Re: Can I build dynamic custom sql query based on user input?
                  kettan

                  This will work, that's what I was thinking of. I was wondering what can I do if I have 3 parameters?

                  You could just add one or more WHEN conditions.

                   

                  Is it advisable to have different data sources for these kind of reports?

                  I don't know your data, and I don't know desired output, and neither do I understand why you want so many parameters. Therefore I am not in a position to give good advises. I had a feeling when reading your original question that you possibly take a more difficult (advanced parameter calculation) and slower (custom sql is slower) route to your destination than necessary.

                   

                  Have you tried to join the tables with Tableau's join dialog?

                  And using quick filters as 'parameters'?

                  If yes, why was that not good?

                   

                  Table Size:             How many rows are in your tables?

                  Column Content:   Are the filtered columns categories or free text?

                                                  How many distinct values (dimension members) do they contain?

                  • 6. Re: Can I build dynamic custom sql query based on user input?
                    Gagan Thapar

                    There are around 30 million rows. This is my understanding of using filters:

                     

                    1. Join tables using tableau join and put filters to get only the relevant data. In my case there are around 30 million relevant rows.

                    2. There is one more type of filter that you can put on reports. e.g. I have pulled all the relevant rows and then I can use filter in my reports.

                     

                    But I don't want to load all the data and then use filters. Can I also use filter as a parameter?

                    1 of 1 people found this helpful
                    • 7. Re: Can I build dynamic custom sql query based on user input?
                      kettan

                      There are around 30 million rows.

                      This helped me to understand 

                       

                      OBS. What I answer below is how I think filters work, but I could be wrong!

                       

                       

                      HOW MUCH IS RETURNED?

                       

                      True, there is a cost with filters, namely separate queries to populate them.

                       

                      This isn't a return of 30 million rows, but distinct lists of dimension members that might be counted with our fingers. If these columns are indexed in Oracle or reside in small joined lookup tables, the performance hit is possibly small.

                       

                      Number of rows returned will be limited to various row-level filters, such as quick filtered dimensions, and never 30 millions unless every filter is set to (All).

                       

                      I wish I could test the performance before answering here, but can't, because my Tableau license is an expired version (9.0) of Desktop Personal, which can't connect to databases. In addition, I don't have any big database on my laptop although such probably could be downloaded somewhere.

                       

                       

                      HOW TO ENSURE THAT MILLIONS OF ROWS ARE NOT RETURNED?

                       

                      In regard to the risk that users can choose (All) for all filters, I do understand why you considered custom sql parameters as a way to hinder millions of rows being returned. Even so, it may or may not be the best solution.

                       

                      Sadly I don't really know what to say more than that the same calculation could be stored in a calculated field and thus still allow you to use Tableau's join dialog.

                       

                      If you ask a NEW FORUM QUESTION about this, you might get some good answers on it.

                       

                      If you do, let me know, because I would like to see what the experts say.

                       

                      We might also be so lucky that some experts read our dialog and share their knowledge here.

                      • 8. Re: Can I build dynamic custom sql query based on user input?
                        Gagan Thapar

                        Thanks for your help on this. So you want me to ask the same question again ?

                         

                        I have tried to get the data greater than 50 million rows with the filters but I see performance issues.

                        1 of 1 people found this helpful
                        • 9. Re: Can I build dynamic custom sql query based on user input?
                          kettan

                          Thanks for your help on this. So you want me to ask the same question again ? 

                             Maybe not.

                           

                          I have tried to get the data greater than 50 million rows with the filters but I see performance issues.

                          Maybe your parameter strategy is the best solution after all 

                          You could use a calculated field and thus get the benefits of Tableau's Join Dialog:

                           

                          IF LEN([Parameter 1]) = 0 AND LEN([Parameter 2]) = 0 THEN ''

                          ELSEIF LEN([Parameter 1]) > 0 THEN [x.b]

                          ELSE [y.c]

                          END

                          =

                          IF LEN([Parameter 1]) = 0 AND LEN([Parameter 2]) = 0 THEN ''

                          ELSEIF LEN([Parameter 1]) > 0 THEN [Parameter 1]

                          ELSE [Parameter 2]

                          END

                           

                          If it was possible, the calculation would utilize indexes if written as [field] = [parameter].

                          I don't know if it is possible.

                           

                          Would a Tableau Server extract be an option, or must it be a live connection?

                           

                          Is the Oracle database a data warehouse or OLTP production database?

                           

                          If data warehouse, you might consider utilizing Oracle Database In-Memory.  I have no experiences with this (just found it by googling) and therefore not saying this as a recommendation.

                          • 10. Re: Can I build dynamic custom sql query based on user input?
                            Gagan Thapar

                            I will try doing that the calculation field you suggested. Most of my reports are on Oracle data warehouse and I have used Tableau extract for them since I don't have to have the production data. But I don't use extract for the reports that run on production. I need a live connection for those reports and charts.

                             

                            I am new to Tableau so I am still exploring the awesome tool. I heard there's a lot you can do with Tableau.

                            • 11. Re: Can I build dynamic custom sql query based on user input?
                              kettan

                              I was wondering what can I do if I have 3 parameters?

                              Assuming you only want to filter one column at a time with parameters to ensure that the number of rows is limited, you could use a (field, value) pair of parameters rather than one for each column.

                               

                              CASE [Field]

                              WHEN "x.b" THEN [x.b]

                              WHEN "x.c" THEN [x.c]

                              WHEN "y.b" THEN [y.b]

                              WHEN "y.c" THEN [y.c]

                              END

                              =

                              CASE [Field]

                              WHEN "x.b" THEN [Parameters].[x.b]

                              WHEN "x.c" THEN [Parameters].[x.c]

                              WHEN "y.b" THEN [Parameters].[y.b]

                              WHEN "y.c" THEN [Parameters].[y.c]

                              END

                               

                              Below is a gif of attached workbook. This could no doubt be done more user friendly with techniques such as  We made a video of Sheet Swapping and Legend/Filter Popping on a dashboard. with Joe Oppelt  and  Matt Lutton. I haven't learnt myself this though and therefore not in a position to teach how.

                               

                              thread 213836 Can I build dynamic custom sql query based on user input.gif

                              • 12. Re: Can I build dynamic custom sql query based on user input?
                                Gagan Thapar

                                This surely is a good way to achieve what I was looking for. Thank you.