In my first InterWorks blog post, I’ve decided to write about something that I commonly used in other BI platforms. The idea of passing parameters in Microsoft and IBM products is vastly used in reporting. A user can define their parameter before running the report. Examples of such parameters are Start Date, End Date, Region, etc. Using database parameters in Tableau is not ideal, as custom SQL needs to be implemented.

The biggest drawback to using custom SQL as a connection is that it becomes a sub-select to every query Tableau runs against every Tableau view. However, the advantage of using custom SQL and parameters is that they will limit the data returned to Tableau. This method would increase performance in most cases, depending on the back-end data architecture and the size of the data itself. 

A Tableau Use Case

I recently came across a great use case to implement this in Tableau. I was querying over 500 million records using a live connection and custom SQL. The calculations for a particular measure were always dependent on the Start Date and End Date selected by a user. These calculations could have been dynamically calculated inside of Tableau, but the large data set caused performance to take a significant hit.

Since a well-implemented back-end architecture was in place, it was favorable to push all calculation to the database end. Depending on the Start Date and End Date, the data was then limited and easier to query. This process helped in enhancing both performance and security. I was able to define different Region filters in Tableau for different workbooks. This is not the best way to handle security in Tableau, but it’s definitely an option. 

I found a couple of limitations in using parameters in Tableau:

• A user cannot currently multi-select. The parameter pass through is limited to a single selection. You can vote this idea up on Tableau’s website.
• The parameter value must be pre-defined. For instance: If there are only four regions defined initially and a new region is introduced, it will not be passed into Tableau.

Implementing in Tableau

Below are the steps a user would take to implement this in Tableau 8.1:

Step 1

Go to the Data Window and select your connection type. In this case, I’m using SQL Server.

Step 2

Input database credentials and custom SQL, then click on the “…” button.

Step 3

This takes you to another window where you can insert parameters by selecting Insert Parameter.

The Report View