Common Table Expressions (CTEs) are used often when working with SQL databases. However, when connecting to a Microsoft SQL server in Tableau to access data CTEs are unusable when writing Custom SQL Queries. The trick is to enter the CTE as and Initial SQL query, which means that the SQL statements are executed at connect time. This blog will provide an example of using a CTE in an Initial SQL query to connect to a Microsoft SQL Server as intended.
Step 1: In the Data Source page, click on the caret icon by your current connection and click Initial SQL.
Enter the SQL query that contains CTEs as normal in the Initial SQL window that pops up. Crucially, before the final FROM clause type
INTO #temptable
This has now ran the query containing CTEs. Now we just have to return it.
Step 2: Now add a Custom SQL Query and enter the following:
SELECT * FROM #temptable
(Note: you can name the temporary table anything, I have just used temptable as an example)
This will return every field and record from the query containing CTEs and create a table that can be used in Tableau!