2 Replies Latest reply on Jul 19, 2018 4:28 PM by Jonathan Drummey

    Concatenate all selections in a filter and add to parameter

    Antonio Spano

      As a beginner in Tableau, I am dumbfounded that something that seems so easy is so difficult, or is it?

       

      I've attached my workbook. My problem is I need to be able to select multiple values in a parameter because of a workbook I have connected to a SQL stored procedure. I would like the user to select multiple regions, and that fires back to the stored procedure to include only the region's selected. Since I can't select multiple values in a parameter. I need to find a work around.

       

      I figured the easiest way to do this is create a filter, and then a calculated field that concatenates all of the selections made in the filter, and then I can add the concatenated line in a parameter that would fire back to the stored procedure (I will worry about breaking down that line in SQL so no need to do it here).

       

      The issue I am having is the calculated field I am using below becomes a table calculation and an aggregate, and therefore cannot be added to a parameter.

       

      I created this calc field named "Region"

      previous_value(attr([Man Region Name]))+ if index()>1 then ", "+ attr([Man Region Name]) else "" end

       

      and then this calc field named "Region Final" to get my final result (I couldn't do in the same calc field because Tableau doesn't allow previous_value and window_max in the same calc)

      if window_max(len([Region])) = len([Region]) then [Region] else "" end

       

      I've spent a lot of time on this, maybe this isn't even the route to go. But basically all I need is to somehow have tableau concatenate all the selections made in a filter into one line, and then tell the stored procedure what those selections are by using the parameter.

      It seems silly to me if this isn't possible. There has to be a way!!!

       

      I'm on a very tight deadline, can anyone help me???!!!!

       

      Jonathan Drummey I've seen lots of stuff from you on the web similar to this but nothing that's worked, do you have a solution??!!!

        • 1. Re: Concatenate all selections in a filter and add to parameter
          Joe Oppelt

          Using the PREVIOUS_VALUE function as you are doing is the way to create a string of all the values in the underlying table for your sheet.

           

          Note:  This is NOT creating a list of all the values checked off in the filter list.  As an example, if you had values of A-through-Z in some dimension, and also values of 2012-through-2018 in another, of course you could have both filters on your sheet.  Your user might select only 2017 from the year filter, and in that year, all 26 alphabetic values do NOT exist in the data.  (Let's say that Q and X are not in 2017.)  But the user might select A, D, Q, R, X and Z in the alphabetic filter.  Your concatenated string would contain A, D, R, Z.  Notice that Q and X are not in the string, because they are not in the underlying table.


          We don't have programmatic access to the filter structure itself.  We just have access to the date that the filter generates in the underlying table.

           

          So you have the table calc of the concatenated values.  I am assuming you know how you are passing that to SQL.  I have never done that (though I have passed such a value in a hyperlink action, for example.)

          • 2. Re: Concatenate all selections in a filter and add to parameter
            Jonathan Drummey

            Hi Antonio,

             

            I've got a couple of questions to start:

             

            1) It sounds like you're using the same stored procedure as the source for both the initial data and the selected data, so in that case why not just use a regular Quick Filter?

             

            2) Have you determined whether you can use something else besides a stored procedure e.g. directly connecting to tables in Tableau's data window, using a database view, etc.? The idea here would be go get to a place where you can use Tableau's native filtering capability.

             

            Now to do a bit of framing of mental models: The way of thinking that "users do some selections, then maybe some more selections, *and then* we show them the data" is guided by traditional reporting system design. Tableau's model is a bit different, it's more based on Ben Schneiderman's Visual Information Seeking mantra of "overview, zoom & filter, details on demand". For example when I double-click on Sales in Tableau's Superstore data I see a bar chart that sums all sales across the entire data source, and then I can use filters, add additional dimensions to slice the data, if those dimensions are in hierarchies then maybe drill in, etc. So there are parts of the traditional reporting workflow that one might be expecting to see in Tableau that aren't there in the way you might be expecting them. Sometimes we can get to the desired results, sometimes the desired set of parameterization & user experience doesn't match what we can natively configure in Tableau, and in those cases we have to resort to using Tableau's JS API or even just not use Tableau.

             

            The only way I can think of to pull this off inside Tableau given the need to refresh a stored procedure with a list of user-selected values is to use two separate views in Tableau:

             

            View A lets users do the filtering and is set up with a "button" that is a worksheet that implements the string concatenation (see @Joe's notes above on the need to be careful about the values). That button triggers a URL action that uses the concatenated string to set a URL parameter for view B.

             

            View B (could be a worksheet or dashboard) accepts the URL parameter as a Tableau parameter (not a filter) and passes the list of values into the stored procedure that then refreshes the view.

             

            Now view A and B could be all in the same dashboard...view A would be a dashboard with the filters and button, and view B would be implemented and placed in a URL object inside view B.

             

            This is getting a little hack-y and there might be some sub-optimal user experience, so caveat emptor.

             

            Regards,

             

            Jonathan