7 Replies Latest reply on Aug 9, 2012 12:20 PM by Gabriel Gejman

    User defined filters using ALL

    John Hobby

      Hey ya'll,

       

      So, I've been unsuccessful in finding an answer to this question so hopefully, someone can enlighten me as to how to set this up correctly.

       

      Goal:

      Setup user security for a user to only see their information OR for a user to see ALL the areas.

       

      Current Setup:

      To try and help with the concept and see if it can be done, i'll keep it simple.

       

      I have a table of Clinics and a table of User Initial Values (the UIV table we define a userid = x clinic or the default of all).

      So:

      Clinic Table                                             User Intital Values

      Clinic Name  |  Department                       UserID     |     Clinic Name

      Cardiology        Sub-Dept 1                      John               Cancer

      Cardiology        Sub-Dept 2                      John               Surgery

      Cardiology        Sub-Dept 3                      Scott              Cardiology

      Cancer             Sub-Dept 1                      Carrie             ALL

      Cancer             Sub-Dept 2

      Surgery            Sub-Dept 1

       

      Question:

      How do you setup a filter so that when John opens the board it will filter for Cancer and Surgery; Scott filters for Cardiology only; and Carrie should see ALL the clinics (Cardiology, Cancer and Surgery)?  I know you can define this in the user filter and assign each person to a set of clinics - but not feasible with 400+ users.

       

      Current Attempts:

      Calculated Field 'User Clinics' :  USERNAME() = [VUNET_ID]

      * this works well when you add this to the Filter shelf and set it to 'True'.  Only the clinics that the user is mapped to show up.  But if you have ALL mapped as your 'Clinic' it returns 'False' and each user would have to remember to change this.

       

      Calculated Field A  :  if (USERNAME() = [UserID] AND [CLINIC] = 'ALL') then 'False' else 'True' end

      *not sure how I could use this to flag a filter for the clinics

       

      Parameter A  :  listed all the clinics in a parameter, but parameters will only return one value - you can just compare against a list of values

       

      So, I'm currently stuck at 50%.  I don't want to have to map each user to a series of clinics (12-18 clinics for each user with 400+ users ... yeah, that's a big lookup table).

       

      Thoughts on if this is possible would be greatly appreciated! 

       

      Thanks for ya'll time!

      John

        • 1. Re: User defined filters using ALL
          Tracy Rodgers

          Hi John,

           

          I'm not sure what you mean when you say "this works well when you add this to the Filter shelf and set it to 'True'.  Only the clinics that the user is mapped to show up.  But if you have ALL mapped as your 'Clinic' it returns 'False' and each user would have to remember to change this."

           

          Don't you want for the user to only see the Clinics that are available to him? If you are referring to the quick filter, you can right click on it so that it show Only Relevant Values, therefore John won't see Cardiology when he opens the workbook.

           

          Let me know what I'm missing!

           

          -Tracy

          • 2. Re: User defined filters using ALL
            John Hobby

            Tracy - Thanks for the reply

             

            Being able to filter for the only relevant values works fine.  The issue is when someone should be able to see 'all' the clinics.

             

            Example:

             

            John     Cardiology

            John     Dermatology

            Jeff       All

            Scott    All

             

            When John logs in, he should only be able to select between Cardiology and Dermatology.  However, when Jeff or Scott logs in, they should be able to see all the clinics in the list.

             

            Calculated Field A  :  if (USERNAME() = [UserID] AND [CLINIC] = 'ALL') then 'False' else 'True' end

             

            If this is put in the filter panel and set to true, John can see Cardiology and Dermatology, but Jeff and Scott see nothing because there isn't a clinic called 'All' - they need the full list.

             

            Thanks,

            John

            • 3. Re: User defined filters using ALL
              Andrew Watson

              It sounds like you need to create a Clinic called ALL in your dataset. In other words, assuming you're familiar with basic SQL, in your data connection SELECT everything at the clinic level of detail, as you are currently doing, then UNION this aggregating all of the clinic data and setting that field name to ALL.

               

              If this isn't clear, or you're not familiar with SQL please attach your workbook (in v6.1 or less if you want me to look at it as my company isn't on 7 yet) and either I or someone else on this forum can write it for you.

               

              Andrew

              • 4. Re: User defined filters using ALL
                John Hobby

                Hey Andrew - thanks for the reply.

                 

                That would almost work .  However, with the UNION in sql, you would get a single clinic called 'ALL' that would total up all the other clinics into a single set of measures. 

                 

                What I'm after is a list of ALL the clinics, not a summary of them.

                 

                 

                I have a table of Clinics and a table of User Initial Values(UIV)  (the UIV table we define a userid = x clinic or the default of all).

                So:

                Clinic Table                                             User Intital Values

                Clinic Name  |  Department                       UserID     |     Clinic Name

                Cardiology        Sub-Dept 1                      John               Cancer

                Cardiology        Sub-Dept 2                      John               Surgery

                Cardiology        Sub-Dept 3                      Scott              Cardiology

                Cancer             Sub-Dept 1                      Carrie             ALL

                Cancer             Sub-Dept 2

                Surgery            Sub-Dept 1

                 

                So, in this example ... when John opens the book - his clinic filter choices should only be Cancer and Surgery.  When Scott opens the book his clinic filter choices should only be Cardiology.  When Carrie opens the book she should be able to see Cancer, Surgery, Cardiology, and the rest of the clinic  (thus a way for a filter to see the value of ALL and display all the individual clinics or see a single or multiple clinics and display only those)

                 

                Let me know if I need to try and explain it a different way 

                 

                Thanks,

                John

                • 5. Re: User defined filters using ALL
                  Andrew Watson

                  Ok, I think I get what you're after - you basically want to avoid creating the large lookup table :-)

                   

                  Can't blame you for that but I don't know any other way to achieve what you're attempting here unfortunately. I don't know if the bulk of your 400 users should be able to see 'all' but if so it might not be as painful to create as you're imagining - get the users with 'all' rights in 1 table, all of the clinics in another and do a SQL cross join - this will give you a new list with all of the 'all' users and all clinics.

                   

                  Hope that helps a bit, otherwise good luck, hopefully someone else will be able to come up with a solution.

                   

                  Andrew

                  1 of 1 people found this helpful
                  • 6. Re: User defined filters using ALL
                    John Hobby

                    Yeah, not after a large lookup table and don't want to manually map everyone who has an ALL to the 30+ clinics.

                     

                    However, your idea of a cross join of users with all to a clinic table to map them on the fly might work better than trying to figure out how Tableau can filter/unfilter against the ALL value.  I'll play around with some cross joins and see how that may resolve this ... try to do more in an oracle query than have Tableau do the calculations/filters.

                     

                    Thanks

                    John

                    • 7. Re: User defined filters using ALL
                      Gabriel Gejman

                      Hi John, did you manage to solve this? Shouldn't the Calculated Field be something like this:

                       

                      if ([User_Clinic] = 'ALL') then True else

                            if (USERNAME() = [VUNET_ID]) THEN True ELSE False

                           END

                      end

                       

                      I mean, if the user has ALL then the Calculated Filed should return TRUE for all cases, and if the Clinic is not ALL then it depends on the user permissions.

                       

                      probably the fields are not correct, I couldn't get from your sample data what are the correct fields to use.