I recently held a live q a session and one of the questions was around custom SQL in this video let's take a look at how we can use custom SQL with Tableau let's also differentiate this from initial SQL and we're also going to take a look at how we can use stored procedures and parameters with our custom SQL put chapters in the description below so you can easily jump to topics you need to refer to let's first look at the setup for this demo we will use SQL Server let's connect to SQL Server I've created a database here called Superstore which contains our Superstore data set from Tableau so if we expand databases we can find superstore if we expand superstore we can see three tables orders people and returns these correspond to the tabs we can find in the superstore Excel file let's briefly look at the content of the orders table we can right click select top 1000 rows and from here we can see the records I also created a simple stored procedure so we can demonstrate how to connect the stored procedures in tableau this stored procedure simply returns order details based on a product name or a part of a product name we will look at this in more detail later on so now let's try to connect to the SQL Server database from tableau in this connection screen we can scroll down to the section that says to a server here we can easily see Microsoft SQL Server however if it's not in the initial view we can simply click on more and search for SQL server in the search bar let's now connect here we get prompted for the server right now I only have a local server so it's enough for me to type in in parentheses local if it's not a local server you simply need to know the connection details it might be the fully qualified domain name or simply a server name if it's in the same network right beside General is another tab called initial SQL we'll take a look at this later on because this is different from Custom SQL let's go back to General right now my authentication is also set to what's called Windows authentication this is also called trusted connection if you click on the drop down there is another option to connect using a specific username or password if this is how your database is set up you simply need to connect using your username and password for now let's click on sign in right now we're connected to the superstore database because that's what we specified in the initial connection screen however if we need to change the database we can simply click on the drop down in the database section and select the database that we want to connect to in the sidebar we're also going to see a couple sections one for tables and another one for stored procedures we'll focus on tables first using new custom SQL and later on we're going to take a look at stored procedures so for now let's drag new custom SQL and what this opens up is simply an editor where we can type in a valid SQL statement for example maybe let's type in select top 10 star from orders and now let's click ok right now we have a custom SQL that simply pulls in 10 records from our orders table let's take a look at this so under sheet 1 first let's move row ID to the top because this is a dimension not a measure and let's try to display this let's drag in our row ID and perhaps the corresponding order ID so we can see that there are only 10 records because that's what we specified in our custom SQL now let's go back to our custom SQL and make some adjustments so going back to the data source so on the drop down edit custom SQL query and this time around let's try to insert a parameter instead of select top 10 star perhaps we want to identify the number of Records we want to bring in so let's remove top 10 and in here we're going to specify where row ID is less than or equal to and in here we're going to introduce a parameter we can insert a parameter that is being passed to our live connection so let's call this Pro ID parameter instead of a float we're going to use integer and for now let's click ok so what really happened so this parameter has been introduced to our custom SQL and this would have also created a parameter in our workbook so let's click on OK first let's create a brand new sheet we can see in the sidebar that there is a new parameter let's show this parameter right now it's giving us one which means there's only a single row ID if we were to display this however if we change the value if we want this to be 10 then it's going to pull in 10 records from our live data source if we change this to 20 then it's going to pull the 20 values so this is how custom SQL Works let's now take a look at the stored procedure this stored procedure simply returns some order details based on a product name if we take a look at the where Clause though which is the Clause that the filters records we specify where product name like and then we have a percent symbol followed by a parameter name called Product name and Then followed by a percent symbol again the percent symbol is a wild card which will match zero or many characters essentially what we're looking for is where the product name value exists in the beginning in the end or somewhere in the middle of the product name this is similar to looking for a substring so now let's connect to this stored procedure let's connect to SQL Server again let's sign in and from here let's drag over the stored procedure we can see this prompt asking us for a value for product name again where does this product name come from this product name is the parameter we've defined in the stored procedure header what we can do in Tableau though is instead of typing a static value is we can create a parameter as well on the drop down we can create a brand new parameter so let's click this let's give this a name let's say product name parameter and for example let's start off with a value clock and let's click ok and OK one more time let's create a brand new worksheet and let's take a look at the results that are being returned to us when we pass in the value clock so go to sheet1 let's move row ID again to Dimensions let's display our row IDs it looks like we have 184 records that have clock in the product name that's display our product name as well let's extend this notice in here that the product name parameter we specified when we were connecting to this stored procedure has been created in Tableau so we can simply show it as well so right click on the parameter show parameter if we want to change the value we can simply change the value for example refrigerator now we can see that the results have been changed so for refrigerator we have 29 records let's try this one more time if we specify Apple for example we can see that the number of Records also change there's 34 marks using the stored procedure will have a little bit of limitation though right now we are connected to the database using a live connection this is going to pose some challenges if we want to create some extracts so let's give this a try let's create an extract right click extract our data so just recall that extracts the way they work is it actually pulls data from your data source into a local proprietary file in tableau if we decide to change the value that we're looking for for example let's search for refrigerator again we are going to get this issue it's going to tell us that the parameter is referenced by the connections custom SQL and it will need to refresh the extract because our local file only retains the records that we had at that point in time if we change our parameter it means that those values are no longer in that local file we need to re-extract them from our live database let us now take a look at initial SQL how is this different from Custom SQL let's connect to SQL Server again and in here let's click on the initial SQL tab so in this editor we can type in any SQL statements that we want to be executed the moment we connect to the database this is different from the custom SQL query that will be used to pull data and create a new data source for us a lot of times we're going to use initial SQL if we needed to prepare the data or set some settings in our database you're also going to notice that there's a learn more link let's open this up this article explains in depth what initial SQL is in addition it also provides examples of how we might decide to use initial SQL so I'm going to Simply scroll down so from here we can see that there are certain parameters that are supported in an initial SQL statement including the Tableau server user the Tableau app Tableau version the workbook name let's scroll down a little bit more and here's a perfect example using Microsoft SQL server in SQL Server we can do impersonation based on a username and in here we are seeing this execute as user and it's going to be based on who is connecting through tableau now let's take a look at another example let's go back to tableau and here let me paste an initial snippet of code so perhaps when we first connect to SQL Server we might want to capture certain information about the current session perhaps this is something that we're going to use in the database itself maybe it's for logging notice as well that there's this insert drop down right underneath the editor and these are all the parameters that can be used within the initial SQL statement and again we can use this and pass this over to our database if we need to so what does this initial snippet do it's creating a local temporary table it has four columns for username Tableau version Tableau app and Tableau workbook name and as we scroll down the second part of this snippet is populating that temporary table in here we are simply inserting these environment values Tableau server user full name the Tableau version the Tableau app and the workbook name so let's sign in so right now before we make any connection to the database let's go back to management Studio in SQL Server All Temporary tables will sit under the system databases so if we expand this and go to tempdb under temporary tables we should be able to see the temporary table we've just created when we connected from Tableau we can't really query this table but we can use it within the same session we had from tableau so let's try this out I'm going to drag over a new custom SQL this time still a select top 10 star from orders and let's just do a Cartesian product so that we can display all the values from that temporary table comma pound underscore session and let's click ok let's go to a worksheet and in here we can see some new fields we have the Tableau app Tableau version Tableau workbook name as well as the username so if we ever needed to display this all of these are currently saved in a temporary table in the database and we can also display that from within tableau so for example order ID and in here we can have the Tableau app it's Tableau desktop you have the Tableau version as well as the Tableau workbook name and that's it for custom SQL I hope you found this session helpful and I hope this answers the question from the live q a thank you again so much for your time and I'll see you again next time