13 Replies Latest reply on Jun 13, 2013 3:22 PM by Alex Kerin

    Creating Parameterized Custom SQLs

    DhanabalaSingam Jegannathan

      Hi All,

       

      Can anyone be able to help me on how to create Parameterized Custom SQL. My requirement is below.

       

      I have an scheduled data extract which running everyday in tableau with incremental refresh using Custom SQL. This SQL generates the data based on the date. ( SQL Logic: BETWEEN Sysdate -10 and Sysdate)

       

      I want to create a Custom SQL to get the date as parameter. I have read and heard that it is possible in Tableau 8. Can someone explain me how to create parameters for an Custom SQL and apply the parameters in Custom SQL.

       

      Thanks,

      Singam

        • 1. Re: Creating Parameterized Custom SQLs
          Alex Kerin

          Presumably you googled paramaters in custom SQL? http://lmgtfy.com/?q=tableau+custom+sql+parameters

           

          Now, with that said, your request I don't think is possible with a parameter, because a parameter does not have the concept of a relative date; that can only be achieved with a calculation, and you can't use those in the SQL.

           

          However, upon a cursory look it seems that some types of SQL (including Jet possibly) do have a current date function so you can create a where statement that only brings this range back.

          • 2. Re: Creating Parameterized Custom SQLs
            Alex Kerin

            Here is a thread on JET current dates - it looks to be either date() or now() that would then be combined with datediff: http://stackoverflow.com/questions/2136552/getdate-equivalent-for-jet-access-database-need-last-month-records

            • 3. Re: Creating Parameterized Custom SQLs
              Alex Kerin

              And.. confirmed to work - here's the superstore data set that only brings in rows from relatively 12 months ago (there wasn't any data for this month it would seem in this data set)

               

              SELECT [Orders$].[City] AS [City],

                [Orders$].[Customer Name] AS [Customer Name],

                [Orders$].[Customer Segment] AS [Customer Segment],

                [Orders$].[Discount] AS [Discount],

                [Orders$].[Order Date] AS [Order Date],

                [Orders$].[Order ID] AS [Order ID],

                [Orders$].[Order Priority] AS [Order Priority],

                [Orders$].[Order Quantity] AS [Order Quantity],

                [Orders$].[Product Base Margin] AS [Product Base Margin],

                [Orders$].[Product Category] AS [Product Category],

                [Orders$].[Product Container] AS [Product Container],

                [Orders$].[Product Name] AS [Product Name],

                [Orders$].[Product Sub-Category] AS [Product Sub-Category],

                [Orders$].[Profit] AS [Profit],

                [Orders$].[Region] AS [Region],

                [Orders$].[Row ID] AS [Row ID],

                [Orders$].[Sales] AS [Sales],

                [Orders$].[Ship Date] AS [Ship Date],

                [Orders$].[Ship Mode] AS [Ship Mode],

                [Orders$].[Shipping Cost] AS [Shipping Cost],

                [Orders$].[State] AS [State],

                [Orders$].[Unit Price] AS [Unit Price],

                [Orders$].[Zip Code] AS [Zip Code]

              FROM [Orders$]

              Where datediff('m',[Order Date],now())=12

              • 4. Re: Creating Parameterized Custom SQLs
                DhanabalaSingam Jegannathan

                Thank you Alex. But my query is scheduled as an Incremental extract . I am Oracle tables and logic is

                "WHERE REPORT_DATE BETWEEN TO_CHAR(Sysdate-30,'YYYYMMDD') AND TO_CHAR(Sysdate,'YYYYMMDD')

                 

                This query is scheduled in Tableau and the extract is executing for 30 days and newly inserted records are added to the extract because of the incremental extract.

                 

                What i would like to know is.. i hear from somewhere that by using parameter we can run the custom SQL where it will run only for missed out days or newly inserted records in table.

                • 5. Re: Creating Parameterized Custom SQLs
                  Harshita Dubey

                  Hi,

                   

                  Probably you are looking procedures for "scheduling updates to published extracts". This is available in Tableau sever 8, where you can add the published workbook for an update schedule & only required extraction is refreshed each week or month with updated data from the data source.

                   

                  I am not sure if this meets your requirement.

                   

                  Here you go for link:

                  http://kb.tableausoftware.com/articles/knowledgebase/schedule-extract-updates-60

                   

                  Regards

                  Harshita

                  • 6. Re: Creating Parameterized Custom SQLs
                    DhanabalaSingam Jegannathan

                    Thanks all for your Answers and suggestions. I got to know that "Parameters would not create the desired effect. Parameters can be used in place of constant values in calculations, filters, and reference lines. Parameters can only relate to data once it has been imported into Tableau Desktop. Parameters cannot be used to set the scope of data connection"


                    Thanks,

                    Singam

                    • 7. Re: Creating Parameterized Custom SQLs
                      Alex Kerin

                      That statement is not true for version 8 where you can use parameters in the custom SQL. I still don't know if one can solve your issue though.

                       

                      Edit :rereading your original question it seems you want the result of the SQL to generate the contents of a parameter? This isn't currently possible and is related to the desire to have dynamic parameters.

                      • 8. Re: Creating Parameterized Custom SQLs
                        Daniel  Paduck

                        How can you use parameters in custom SQL using Tableau 8?  I basically have the same issue where I basically want to do something like this:

                         

                        Select FirstName, LastName From EM where Employee = @Employee.

                         

                        I do not want to use a quick filter because this will not solve my issue.

                         

                        Thanks!

                        • 10. Re: Creating Parameterized Custom SQLs
                          Daniel  Paduck

                          Hi Alex,

                           

                          Thanks for the link.  Do you know if it is possible to create cascading parameters or dynamic parameters?  By cascading I mean something like the first parameter is Fiscal Quarter and the second parameter is Months. Thus, the first parameter would have Q1, Q2, Q3, Q4 as values and the second parameter would have for available values either Jan, Feb, March for if the user selected Q1.

                           

                          By dynamic parameters I mean the parameters are refreshed automatically when the data source is refreshed.  Thus, if I create my parameter values as a list and from 'add from field' that list is updated as long as the data source is updated.

                           

                          Lastly, multi valued parameters - this would nice to have but I am not sure if it is possible.

                           

                          Thanks!

                          • 11. Re: Creating Parameterized Custom SQLs
                            Alex Kerin

                            You could have multiple parameters like that, but they won't self filter. That's where quick filters with "Show only relevant values" (or something like that) come in. Why do you need to use parameters?

                             

                            Dynamic parameters are one of the most requested ideas on the forum, so the answer at the moment is no. Multi-valued parameters - do you mean that two values can be selected? If so, no, what's your use case for this?

                            • 12. Re: Creating Parameterized Custom SQLs
                              Daniel  Paduck

                              I would like to use parameters to limit the dataset rows which would in turn limit what the user sees in the quick filter values.

                               

                              I am looking for Multi-Valued parameters. For example, in a scenario I want to limit the data set to return only rows where certain project managers have been assigned to project.

                               

                              I have found that the 'Show Only Relevant Values' works good when the filtering is going from top down but it seems to filter going from bottom up and thus makes it confusing for the user to be able to use. For example, I have a report which lists for example offices by region.  I have for example, North East, South East. as one filter and then the office as the second.  So, the user will go in a click on North East, the user will then see offices in the second filter for Office like Boston, New York. However, if the user tries to go back and change his/her selection to 'South East' in the first filter that option is not there b/c he/she already choice Boston as a choice in the second filter.  I find this to be rather challenging for the user to get through.

                               

                              Have you used Qlikview?  I know we are not going down this road within my company but just wondering if it is more developer friendly.

                              • 13. Re: Creating Parameterized Custom SQLs
                                Alex Kerin

                                Your first quick filter should be set to show all values.

                                 

                                I have not used Qview