Custom SQL in Tableau

What is custom SQL in Tableau?

Custom SQL in Tableau, also known as cSQL, refers to the object model that allows Tableau developers to create customized datasets based on the underlying dataset. Custom SQL allows developers to create flexible, granular, or aggregated datasets and apply data transformation and calculations to underlying datasets as per their reporting requirements. It neither alters the underlying dataset nor changes any structures of the underlying dataset. Using custom SQL, developers can apply various operations such as data filtering and aggregations, alter the field attributes run time, and add customizations to your data.

A sample custom SQL in Tableau is provided below:

SELECT 

[Superstore Dataset].[Category] AS [Category],

COUNT([Superstore Dataset].[Customer ID]) AS [Total Customer],

        COUNT([Superstore Dataset].[Order ID]) AS [Total Orders]

FROM [Superstore Dataset]

where [Superstore Dataset].[Category] = ‘Furniture’

group by [Superstore Dataset].[Category]

How to set up custom SQL in Tableau?

To set up custom SQL in Tableau, follow the steps below.

Step 1: Connect to your dataset (typically a database containing the physical data attributes) from the Tableau interface. You can use different databases as per your data storage.

For this demo, we have used the Microsoft Access database. You may use other databases, such as MySQL, PostgreSQL, Denodo, MemSQL, etc.

Once the data is imported to Tableau, you will be able to view it in the Data Source tab.

Step 2: Right-click on the Superstore Dataset in the Tableau view and choose Convert to Custom SQL.

Tableau will open a Convert to Custom SQL screen with the SQL statement shown below.

 Step 3: Specify the custom SQL query in the text box as shown below. 

Here, we have defined a custom SQL query to display Total Customers and Total Orders per Category and Sub-Category combinations.

Click on OK to save the changes.

Once you click OK, Tableau will process the query and you will see the custom SQL query table visible in the logical layer of the Tableau canvas as shown below. Note that only the fields defined in the custom SQL query are displayed in the data grid on the Data Source page.

How to modify queries with custom SQL in Tableau?

To modify or edit queries with custom SQL in Tableau, follow the below instructions:

Step 1: Open your existing data connection in Tableau.

Step 2: Navigate to the Data Source tab. In the canvas, double-click on the logical table to view the physical table, as shown below.

Once you double-click on the logical table, you will see the Custom SQL query in the logical layer in the canvas.

Step 3: Hover over the Custom SQL Query in the physical layer in the canvas until you see the arrow. Click on the arrow and choose Edit Custom SQL Query.

Step 4: Edit the custom SQL query as per your reporting requirements. 

Here we have added SUM(Profit) to display the total profit for each category and sub-category combinations.

Click on OK.

Tableau will run the amended query to display the results as per the defined SQL statement.

Step 5: You can also rename or remove the custom SQL query. To do this, click on X to close the window.

In the next section, we will look at some examples of how to expand custom SQL to use other features in Tableau. 

Examples

In this section, we have demonstrated different examples of using custom SQL in Tableau to apply renaming, removing queries, and using the parameters in custom SQL.

Example #1 – Renaming Queries with Custom SQL in Tableau

In this example, we will demonstrate how to rename queries with custom SQL in Tableau using the IEA Global Data 2024 dataset. The IEA Global Data 2024 dataset collects prices of electric vehicles, including Battery Electric Vehicles (BEVs), Fuel Cell Electric Vehicles (FCEVs), and Plug-in Hybrid Electric Vehicles (PHEVs), across different geographies and years around the world.

Follow the steps provided below to rename queries with custom SQL in Tableau:

Step 1: In Tableau Public, click on Data – New Data Source.

Step 2: In the New Data Source window, specify the details of the database file. Here we have provided the path of the MS Access database file. If you are using any other file, ensure you have provided the desired database credentials to access the dataset.

Once the data is successfully imported, you will be able to view it in the Data Source tab as shown below.

Step 3: Right-click on the IEA Global EV Data 2024 in the Tableau view and select the Convert to Custom SQL option.

You will notice custom SQL in Tableau desktop with SQL SELECT statement as shown below.

Step 4: Define the custom SQL query for your dataset in the text box. 

Here we have defined a custom SQL query to display the Total value of EVs per region, category, mode, and unit combinations for Vehicles unit.

Click on OK.

Once you click on OK, Tableau will run the SQL query to display the custom data in the view, as shown below.

Note: You can also view the custom SQL query table with the fields in the logical layer of the Tableau canvas.

Step 5: On the Data Source tab, in the logical layer of the canvas, click on the arrow next to the dataset, i.e., IEA Global EV Data 2024, and then choose Rename.

Step 6: Provide a meaningful name to the table as shown below.

Here, we have renamed the query to IEA Global EV 2024 Dataset. Tableau will automatically amend the changes to the query.

Example #2 – Removing Queries with Custom SQL in Tableau

In this example, we will provide a step-by-step guide on how to remove queries with custom SQL in Tableau. To demonstrate this, we have used Google Play Store Apps Data. The Google Play Store Apps dataset contains detailed information on different apps, app categories, app store ratings, user reviews, app size, total downloads, Free/Paid, App price, etc., available on the Google Play Store app.

To remove existing queries with custom SQL in Tableau, follow the instructions outlined below:

Step 1: Import the Google Play Store Apps dataset into the Tableau Public using the Data – New Data Source. Provide the required credentials for your database for Tableau connection.

Step 2: In the Tableau view, hover over the Google_play_store dataset and click on the arrow. From the context menu, select the Convert to Custom SQL option.

A custom SQL in Tableau desktop will be displayed in the text box as shown below.

Step 3: In the Convert to Custom SQL text box, specify the condition for SQL query for your dataset.

Here, we are applying a data filter to extract only apps with a rating value of 4.5 or above.

Tableau will display only those apps that have a rating value of 4.5 or more.

Step 4: Right-click on the Google_play_store table in the Tableau view and select Remove.

Tableau will remove the custom queries with SQL as shown below.

Example #3 – Using Parameters with Custom SQL in Tableau

In this example, we will create custom SQL using parameters in Tableau. For this demo, we will use the Cost of Living Index by Country dataset, which provides the cost of living index relative to New York City (NYC), with a baseline index of 100% for NYC. The dataset contains key information such as the Rent Index, Groceries index, Restaurant index, local purchasing power, etc., for different countries. 

To create a custom SQL using parameters in Tableau, follow the instructions provided below:

Step 1: Connect the Cost of Living Index by Country data to Tableau Public or Desktop using the New Data Source interface.

Once the data is successfully imported, you can view it on the Data Source tab.

Step 2: In the Tableau canvas, right-click on the logical table i.e. Cost_of_Living_Index_by_Country_2024, and select Convert to Custom SQL.

Step 3: In the Custom SQL text box, specify the custom SQL query. 

Here, we have created a custom SQL query to display the dataset based on the country’s ranking using dynamic rank values.

For this, we need to add a parameter to the where clause.

Click on Insert Parameter – Create a New Parameter.

Step 4: In the Create Parameter, provide a name. Specify the data type and display format.

Click on List as Allowable values. We have specified 3 values i.e. 10 as Top 10, 50 as Top 50, and 100 as Top 100. Click on OK to save changes.

Now you can see the parameter is added to the where clause.

Step 5: In the new worksheet, right-click on the parameter and Show Parameter.

Step 6: Drag the Country dimension to the Rows. Then, drag all the measure values to the Text on the Marks pane.

Step 7: Change the Rank Parameter from Top 10 to Top 50. Tableau will run the query using the input parameter and refresh the Tableau view as per the parameter value.

How to troubleshoot performance issues in custom SQL in Tableau?

To troubleshoot custom SQL in Tableau performance, some of the below tips can be applied:

• Create a materialized view in the database to improve performance.
• Reduce the number of joins to as few as possible.
• Review and optimize the custom SQL in Tableau performance by enhancing the query.
• Consider using the Tableau extracts to aggregate the data.
• Work with your DBAs to tune your database performance.

Important Things to Note

• As a best practice, you should define column aliases with an AS clause in your custom SQL query. Otherwise, Tableau will generate a column name automatically.
• Custom SQL queries may result in errors if it references duplicate columns. Make sure there are no duplicate columns in your queries.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to What Is Custom SQL in Tableau. We learn how to set up custom SQL, modify queries, how to troubleshoot performance experiences, with examples, and points to note. You can learn more from the following articles –

Hierarchy in Tableau

Tableau Reporting

Tableau String Functions