I'm not aware of any Tableau specific limitation because Tableau simply passes the custom SQL to the underlying data source. At that point, custom sql may run into issues based on the underlying data source's limitations. This Microsoft documention outlines the limitations for MS Excel. Excel specifications and limits - Excel Does this help to explain the issues you are experiencing?
Why are you using the legacy Excel connector and custom SQL at all? The native connector and the union feature will do this for you with fewer problems.
This was just an example. I needed to select some specific fields from a large spreadsheet.
I am trying to use Tableau Custom SQL with a Microsoft SQL Server connection. I don't think the custom SQL is simply being passed to the DB, because when I try to run custom queries, especially with CTE's (common table expressions) like this, they won't run through Tableau's Custom SQL but they will run on the database itself directly. Any ideas? I tried Data => data server => Run Initial SQL too, but to no avail. - Chris
WITH getMostRecentPID AS (
SELECT TOP 1
,pd.[date] AS productionDTS
FROM [CompanyDB].[dbo].[prod_dates] pd
WHERE released = 1
ORDER BY [date] DESC
results.[value] AS MetricValue
FROM CompanyDB.dbo.results results
INNER JOIN CompanyDB.dbo.tags tags ON results.tag_id = tags.tag_id
INNER JOIN getMostRecentPID g ON g.pid = results.pid
tags.tag_id = 555555
1 of 1 people found this helpful
The custom SQL gets wrapped in a SELECT *, so common table expressions (WITH clauses) do not work. See https://kb.tableau.com/articles/howto/using-common-table-expressions for some examples of how to do it.
Or just inline the common table expression.
Thanks, will take a look.