Tableau has supported Stored Procedures in Microsoft SQL Server (and Sybase and Teradata) since version 8.1, and you can connect the SP parameters to Tableau parameters.

However, there are two features that don’t exist as of 9.2:

1. Parameters cannot be set to match a function, such as USERNAME()
2. Parameters cannot have multiple values (no array concept)

These are both feature requests that you can go vote up on the Community forum, so go there now and then come back and continue reading!

Until these features are implemented, the only way to set these values dynamically is using Tableau Server’s ability to set parameters programmatically.

Setting Values Programmatically

There are two ways to set values programmatically when a viz is embedded.

1. You can specify them in the URL using the parameter name, just like a filter. You can set the parameter value in via the JavaScript API at load time
   • This causes the parameter to have the value prior to viz load, but because the parameter is sent via a URL parameter, the value goes across insecurely in the HTTP header. The JavaScript API can be manipulated, but in an HTTPS setting it shouldn’t pose any security risk in transmission
     
   • Alternatively, rather than passing username across directly, your web application might come up with a unique, one-time use value that can be matched up to a user’s session within the Stored Procedure to reveal the username. This would be similar to how Trusted Authentication works in Tableau, and would allow you to send the value in the URL ahead of the load time, since it would only be useful once and not reveal anything about the actual username value.
   • Note: Give your Parameter a distinct name. If you have a Dimension or Measure Name with the exact same name as a Parameter, this syntax will assume the Dimension or Measure and you’ll be unable to set the Parameter value.
2. You can set the value using the JavaScript API command workbook.changeParameterValueAsync() . This of course only works when you embed the viz using the JavaScript API.
   • This method is totally secure when using HTTPS because the JS API uses POST to send its values, but it must happen after the viz object loads, and it will then cause the viz to reload with the new value. You are better off setting the value in the constructor so that the Viz loads up correctly the first time. Otherwise, you may want to delay showing the Viz window to the end user until it has both loaded AND the filtering has been sent.

Now we have a dynamic way to set parameter values that will go to the Stored Procedure.

Two-Factor Security is Necessary

Because parameters can be set via JavaScript or the URL, they are not a fully secure method of passing information. If someone changes the URL or runs certain JavaScript, they could pass different information in and then get access to information they should be denied.

Luckily, we can use Tableau’s Data Source filters for Row Level security which cannot be tampered with. The Stored Procedure should be built in a way that returns the Username as a field available on every row of the Stored Procedure results.

Then you can create a Calculated Field in the data source like:

[Username Field] = USERNAME()

or alternatively:

IIF([Username Field] = USERNAME(),1,0)

The USERNAME() function will resolve to the Tableau Server username that the user has been logged in through via single sign-on. If this function is put on as a Data Source Filter, it will ensure that only the logged in user can ever see results, preventing tampering with the parameter.

 

Multi-value Parameters

Tableau parameters can only have a single value, but a string parameter can take a long set of values. The simple solution is then to pack in a number of values in a delimited way into a single parameter set as a String, then split them in the Stored Procedure and cast as necessary.

I was initially surprised to find that there is no easy Split function in T-SQL, but I found the second method here (XML) to be a pretty succinct way of splitting a delimited list and creating the kind of table that could be passed into queries:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

With the function added to the server, I was able to pass in a multiple values with comma-delimiting, and then have them used within the query in an IN ( ) clause for filtering.