11 Replies Latest reply on Aug 5, 2016 6:29 AM by Toby Erkson

    Problem with dynamic multi user filter

    Greg Gawinowski

      Hi guys!

       

      I'm new to Tableau and believe me I did my best trying to solve this issue on my own but to no avail.

      I have a problem with user filtering. Well, I have two working solutions, but not the ones I intend to have.

      To quickly describe a situation - I have a very similar dataset to the one in the user filtering tutorial:

      Data Security with User Filters - Tableau 9 - YouTube

       

      Quick description of data. I have hundereds of individaul supersotres and their sales values. Each superstore gets a report where they can see their sales and as a benchmark global sales, country sales and supersotre type benchmark sales. The benchmark names are stored in the same column as supersotre names since I unioned the queires in SQL:

       

      Example:

      select superstorename, sum(sales)

      from Main

      group by superstorename

       

      union all

       

      select countrybenchmark as superstorename, sum(sales)

      from Main

      group by countrybenchmark

      [... and so on]

      The problem I have is that I want to apply specific user to see more than one category. Let's say I have a manager who should be able to see his store sales, global stores sales, USA store sales and Home&Garden sales.

       

      I can (just like in the tutorial above) go through the list and apply manually each 4 categories to each stores - this works well, but for hundereds of clients it would take me a week to apply correct filters.

       

      The second thing I can do is to set a datasource filter username()=[superstorename] but this will only show superstore values not benchmark values unless I can somehow make this formula return a set of values. - this doesn't work for me since I need to show all 4 categories i.e. superstore, global, country and type benchmarks.

       

      Third solution is to do an additional column, named e.g. superstorenamefilter, which for each superstore will cointain only superstorename value, but then in superstore coulmn it will have correct superstorename, and all necessary benchmarks (see attachement - solution 3). This way datasource filter username()=[superstorenamefilter] will return the correct set of 4 categories. This solution also works for me, but the drawback is, that global benchmark is multiplied n number of times where n is the number of superstores and other benchmarks are duplicated numerous times as well. The extract weights nearly 4 gigs and when I need to update it, it's just way too big.

       

      So my question to the community is:

      is there a way to somehow auotmate first solution or edit the formula in second solution that it returns 4 values not just one?

       

      All tricks on the database allowed! I attached an excel worksheet showing how the data looks like and what I mean exactly. I hope this is all clear.

       

      Thanks,

      Greg

       

      P.S.

      My data is way more complicated than that. This is just an example, but the principles are the same.

        • 1. Re: Problem with dynamic multi user filter
          Patrick A Van Der Hyde

          Hello Greg,

           

          I went ahead and moved this post to the Tableau Server Administration area where Tableau Server Administrators are usually assisting users with issues related to Tableau server.

           

          Without seeing the data directly in a workbook it is difficult for me to see how this comes together but thank you for the data sample to assist.  Since this was posted a few weeks ago, have you tried contacting Tableau Support with this isssue?  This is a case where they may be the best resource if additional workbook data is not available to share.   My thought would be to try and create level of detail calculations to determine the aggregate benchmark values for each level but again, I think seeing a small subset of the data would be helpful. 

           

          Toby Erkson - any ideas here? 

           

          Patrick 

          • 2. Re: Problem with dynamic multi user filter
            Matt Coles

            Hey Greg. That's a tricky one. There may be a couple things you could try...and doubtless others will have better suggestions than I do...but first, I have a few questions.

             

            First, it's interesting that you started right out with Custom SQL. Typically with Tableau, you feed it the granular version of the data, then let it do your aggregation work for you. UNIONs aren't possible without Custom SQL at this point, unfortunately, but there are other solutions that may not require it--for example, creating a calculated field that reports the sum of a particular Store Type. So you could basically use each calculation to filter the data differently, so that you could see all the different aggregations.

             

            Secondly--is this data actually sensitive? Meaning, do we actually need to prevent certain people from seeing it? Or is it just a matter of convenience for the user, so that whoever views the workbook doesn't need to pick a bunch of drop-down filters that are relevant to them? Because if it's more the latter, then more options exist for solving the problem.

             

            One way you may be able to solve this problem is by creating a set of data describing the store name (or perhaps store manager name since we're talking about users), the country, and the store type. Then build a dashboard that contains multiple sheets on it, with each sheet "joining" the sales data aggregates to the master person-table via Data Blending. The sheet for the store-level sales data would blend on the Manager Name field. The sheet for country level data would blend on the Country field. The sheet for Store Type data would blend on the Store Type field. And the master person-table would be user-filtered to the Tableau Server or Tableau Online user who was currently viewing the dashboard.

             

            If the database server you're connecting to is one that Tableau supports Initial SQL for, you may also be able to simply pass the username into a query prior to the workbook gathering the sales data, and generate a temporary table that you join in your Custom SQL to trim down the aggregate sales data to just what you want to show. Come to think of it, that's probably a way more elegant solution for you than the blending version--I'd try that one first if you're able!

            • 3. Re: Problem with dynamic multi user filter
              Toby Erkson

              Thanks Patrick for the ping.  Unfortunately this is more of a Desktop problem and thus a weak area for me   However, Matt's evaluation and suggestions sound pretty good and I'd go with what he recommends.

              • 4. Re: Problem with dynamic multi user filter
                Greg Gawinowski

                Hi,

                 

                I am really sorry I didn't answer but I was on holidays for two weeks and I totally missed this afterwards.

                Thank you for all of your ideas. I will try to check whether I'll be able to make them work.

                Matt, yes the data is confidential, so other users cannot see other users data.

                 

                I spoke to one of your consultants though, and I think he proposed a solution which would be great for this and other projects we do. Basically what he has suggested is that the permissions can be set by editing XML. I know there is a special add on for XML, but we've agreed that for the purposes I need, it would be an overkill and there is another way to edit the XML. I understand the risks, but I think this would allow me to automate the process withouth making virtually any changes to the workbooks I already have.

                 

                Could anyone of you just guide me where I can enter the XML view and I am pretty sure I'll find what I need? I've done this before with other programs and I hope I'll figure it out !

                 

                Many thanks,

                Greg

                • 5. Re: Problem with dynamic multi user filter
                  Toby Erkson

                  The .twb -- the workbook -- is an XML file.  Simply open it with your favorite text or XML editor

                   

                  I have no idea what is meant by "...the permissions can be set by editing XML" nor do I know of a special add-on.  What is this add-on anyway?

                  1 of 1 people found this helpful
                  • 6. Re: Problem with dynamic multi user filter
                    Greg Gawinowski

                    Hi!

                     

                    Thank you for your reply. I must have misunderstood what I was told since I got the message that Tableau has some special Tableau XML software (which is paid), but you can enter the XML code from within Tableau itself.

                     

                    I tried to do what you suggested before, but now I saw what the problem was. I was trying to open TWBX file and it must be a TWB file in order to open in XML editor.

                     

                    Ok I just checked it and it works perfectly! Exactly what I wanted and needed

                     

                    Thanks guys!

                    • 7. Re: Problem with dynamic multi user filter
                      Toby Erkson

                      Okay...

                      Yes, the .twbx is a zip file of the data along with the workbook.  If you change the file extension from .twbx to .zip and then open it you'll see what I mean.  So extract this .zip file and you will see the workbook (.twb) and you can edit it from there.

                      • 8. Re: Problem with dynamic multi user filter
                        Shawn Wallwork

                        (And add twbx to your open with file extensions list for your zip utility and you don't even have to change the extension back and forth). Toby made a post that explains how to do this, but I can never find anything on the forums, so he'll have to give you the link.

                         

                        --Shawn

                        • 10. Re: Problem with dynamic multi user filter
                          Greg Gawinowski

                          Hi,

                           

                          Toby I can see you're confused with my answer but the whole thing has been sorted with editing user permissions in XML. I can now add new user filter set members in bulks since this can be even created in excel and then just simply copied and pasted . This is th epart of the code I am talking about:

                           

                          <groupfilter expression="ISCURRENTUSER('local\Tescox')" function="filter">

                          <groupfilter function="union">

                            <groupfilter function="member" level="[Store]" member="&quot;Tescox&quot;"/>

                            <groupfilter function="member" level="[Store]" member="&quot;United Kingdom&quot;"/>

                            <groupfilter function="member" level="[Store]" member="&quot;Grocery Stores&quot;"/>

                            <groupfilter function="member" level="[Store]" member="&quot;Super markets&quot;"/>

                          </groupfilter>

                          </groupfilter>

                           

                          Before I had to manually click and assign members for each client or create huge extracts. Some clients have like 8 groups and often the number of clinets is a three digit number so you can imagine the number of manual assigments.

                           

                          For me, the solution above is close to perfect, at least at my level of Tableau knowledge

                          • 11. Re: Problem with dynamic multi user filter
                            Toby Erkson

                            Ahh...gotcha!  Thanks Greg