6 Replies Latest reply on Jan 17, 2017 1:39 PM by Jatin Kakani

    Using a list of values in a parameter to pass to a stored procedure

    Jatin Kakani

      Hello folks,

       

      I am using a parameterized stored procedure as a source for my report.

      I wish to give the ability to the users to pass a list of values to the parameter of the stored procedure so that they can fetch only the desired data.

      For this purpose, I have created a parameter of type string on my Tableau dashboard.

      However when I copy a list of column values from excel / notepad and paste it in the parameter box only the first value gets pasted. But If I enter multiple values separated by a space, it works fine.

      I understand that it is behaving this way since Tableau parameter doesn't accept multiple values. When I pass the values separated by a space, it is taking it as a single value and my stored procedure is doing the task of separating the values based on space.

      But I'd like to give the users the ability to paste a list of values into the parameter box.

      Can you suggest any work around for the same?

       

      Thanks.

      Regards,

      Jatin

        • 1. Re: Using a list of values in a parameter to pass to a stored procedure
          Jamieson Christian

          Jatin,

           

          When I paste columns of data from Excel, all of the columns come through, but they don't appear to be delimited.

           

          When columns of data are copied from Excel into a text-only receiver, Excel delimits the data with tabs. So I did a quick check in Tableau to see if the tabs were being retained in a parameter after pasting from Excel (even though they aren't visible).

           

          FIND( [Parameter], CHAR(9) )

           

          Unfortunately, FIND() did not locate any tab characters. it appears that Tableau filters out the tabs when the data is pasted in, so all of the values get run together.

           

          I'm not sure what you can do in Tableau to work around this. You may have to incorporate a formula in your Excel workbook(s) to pre-concatenate relevant values with the appropriate delimiter, so that your users have a single cell they can copy-and-paste from.

          • 2. Re: Using a list of values in a parameter to pass to a stored procedure
            Jatin Kakani

            Thank you for your reply Jamieson.

            Yes on Tableau desktop, all the values come through concatenated to each other without a delimiter.

            However when I publish the report on server, just one value gets pasted in the parameter box.

            Anyways in either case, the purpose is not served.

             

            Actually there is no fixed excel file which the users will use to copy the data from.

            In fact, the users may not necessarily copy the column from an excel, it can even be a text file or anything.

            Basically the users will have a list of values(keys) and would want to see only the data for those keys.

            • 3. Re: Using a list of values in a parameter to pass to a stored procedure
              Jamieson Christian

              Jatin,

               

              I'm going to conclude that a Parameter will not behave they way you want it to behave.

               

              As an alternative: is a Quick Filter an option for your dashboard? If so, try a Quick Filter configured to "Multiple Values (custom list)". On Tableau Desktop, this type of filter will properly incorporate a copy-and-paste of multiple values that are linefeed-separated, so it will work if the values(keys) are on separate rows in Excel or separate lines in a text file. It won't work with tab-separated, unfortunately. I'm not sure how it behaves on a published workbook.

              • 4. Re: Using a list of values in a parameter to pass to a stored procedure
                Jatin Kakani

                Hi Jamieson,

                 

                The problem is, users will only be able to filter on the data that has already been pulled.

                However due to the excessive data volume, I want to fetch only the desired data from the database.

                Since the data source filters cannot be accessed by users in a published report, I have used the paramterized stored procedure.

                 

                Jatin

                • 5. Re: Using a list of values in a parameter to pass to a stored procedure
                  Jamieson Christian

                  Jatin,

                   

                  I'm afraid I'm out of ideas. I have some half-formed thoughts about using a second query or materialized view to build the list of unique keys to drive a Quick Filter, and then JOIN that to the main query to limit results, but none of it's fully congealed, and it doesn't strike me as terribly optimal.

                   

                  I still think this very specific scenario might best be served by a helper tool that takes copy/paste values and concatenates everything into a single value that can be copy/pasted into Tableau. Either a specially crafted Excel workbook, or maybe even an HTML file with JavaScript to process the copy/pasted input for the same purpose.

                   

                  Sorry I couldn't be more help.

                  • 6. Re: Using a list of values in a parameter to pass to a stored procedure
                    Jatin Kakani

                    Hi Jamieson,

                     

                    No problem. Thank you very much for your inputs.

                    I'll see if I can find some other workaround or write some script to get the values the way Tableau parameter would allow.

                     

                    Regards,

                    Jatin