3 Replies Latest reply on Jun 25, 2013 12:36 PM by Michael Hetrick

    How do I incorporate a username into a custom SQL query when creating a data connection?

    Michael Hetrick

      I'd like to include the current username into the custom SQL query statement when creating the data connection. For example, how can I have networkid evaluated based on current username:

       

      SELECT * FROM tablename WHERE id IN (SELECT id FROM users WHERE networkid = 'domain\username')

        • 1. Re: How do I incorporate a username into a custom SQL query when creating a data connection?
          Aaron Clancy

          (assuming you're using Tableau Server with AD Authentication)

          If you didn't need it to be in the query you could do a live connection with a join based off of the id field in each of the tables.  select * from tablename a join users b on a.id = b.id

           

          Then create a calc in tableau  -  Userfilter:

          username() = [networkid]

           

          put that on the filter shelf and select "true" for the value.   ("true" wont show up if there isn't an exact match so make sure you're logged in as a Tableau Server User from desktop before you put the calc on the filter shelf)

           

          This is essentially the same thing as modifying the where clause of the query.

           

          You'll have to make sure the value that Tableau is getting for the function "username()" is an exact match for the values that are in the networkid field.  If not, you'll have to have some sort of conversion table.

           

          The way that I am currently doing it (Because I am not using AD Auth)

          I created a web portal in  PHP that gets the username of the authorized user. Embed the visualization in an iframe.  pass  the username in as a URL parameters and that value is inserted into the custom SQL as a parameter in the where clause. 

           

          If you need more detail on the second process let me know (it's kind of lengthy)

          1 of 1 people found this helpful
          • 2. Re: How do I incorporate a username into a custom SQL query when creating a data connection?
            Robert Morton

            I agree with Aaron's guidance, but I recommend using Data Source Filters in this case since the described use case requires that the filter is enforced for all worksheets (and more generally, for all queries).

             

            In Tableau v8.0 or newer, choose the menu item Data >> (data-source-name) >> Edit Data Source Filters....  You can then use the calculated field containing the user calculation as your data source filter criteria.

             

            The advantage of using Data Source Filters is twofold:

            1. you never have to remember to place the user filter on each worksheet, and
            2. you can publish the data source to Tableau Server as a Data Server data source, and allow users the ability to connect/query from Desktop but not download/modify the data source -- this prevents users from bypassing the user filters when working with that data source in Desktop.

             

            -Robert